+1

from squel.

The current query builder logic doesn't anticipate the possibility of building multiple queries in the same query strings (e.g. two SELECT statements in the same query string). Supporting UNION would necessitate more complexity, and I'm not sure if it's worth it, especially given that you could simply use an Array.join() on two built queries to accomplish the same.

from squel.

Wouldn't you just get around this by doing:

"#{squel.select()} UNION #{squel.select()}"

As it is essentially a keyword between two distinct select statements?
I haven't used UNION specifically but I do the same to construct multiple queries for one call like this:

"""
#{squel.select()};
#{squel.select()};
"""

from squel.

Am closing this for now, since existing concat solutions are satisfactory.

from squel.

I have a problem with using the concat method since I want to use the toParam() method to use parameterised queries, thus adding SQL injection protection through the built-in database support.

Have you considered extending the JoinBlock to include a UNION type. UNIONS only differ by the absence of the JOIN and ON clauses. See below for an example change to buildStr. This could be improved upon since there is no condition for a UNION. I have extended the JoinBlock using this method with success with postgres.

Add a UNION with the given table.

union: (table, alias = null, condition = null) ->
@join table, alias, condition, 'UNION'

buildStr: (queryBuilder) ->
joins = ""

for j in (@joins or [])
  if joins isnt "" then joins += " "
  joins += "#{j.type} "
  if j.type isnt "UNION"
    joins += "JOIN "
  if "string" is typeof j.table
    joins += j.table
  else
    joins += "(#{j.table})"
  joins += " #{j.alias}" if j.alias
  if j.type isnt "UNION"
    joins += " ON (#{j.condition})" if j.condition

joins

from squel.

You can still do it with concat, the concat just has an extra step:

p1 = sql1.toParam()
p2 = sql2.toParam()

finalParams =
    text: "#{p1.text} UNION #{p2.text}"
    values: p1.values.concat(p2.values)

from squel.

While your suggestion works, I will argue as a user of a library I like my interfaces to be simple and squel makes things simple by encapsulating the select into a query object. It then makes it easy to perform joins and allows a select to replace the table in the join, so why not extend it further to allow a similar syntax for union, especially when all the hard work has been done making a table name and select query interchangeable.

sql1.union(sql2).toParam()

This is much easier to write and consume as a maintainer of code.

from squel.

I will create a PR when I get time to add some tests and let the maintainers review and decide whether it is worthwhile...

Thanks :)

from squel.

True, I agree with you on that point, that's why I use a custom wrapper package around squel that lets me add random functions I use everywhere to the object (being careful not to override anything important of course). https://gist.github.com/SimeonC/37a3e6395b9b2a740552 if you're curious.

from squel.

The complication in implementing UNION is the fact that you have two separate query builder instances going on. Need to think about how this interaction will work in terms of parameterized queries as well as other things.

I look forward to the PR. Will leave this open for now.

from squel.

This got stuck in my head, one possible solution would be:

Squel.union = ->
    _queries = arguments # allows passing of squel query builder instances as individual args

     toString: -> _queries.join " UNION "
    toParam: -> # code I posted above

This gets around the whole issue of the query builder instances, also as you'd be passing object references it doesn't matter if you edit the query builder instances afterwards as it just "reads" them at the right time.
It's not quite squel.select().union(squel.select()) but I think this better represents what Union does anyway, thoughts? Opinions?

(I'm on my phone so please excuse shorthand and syntax errors)

from squel.

I have added a pull request for review. #93

Please leave some comments.

from squel.

Now available in 3.9.0, thanks @waterytowers

from squel.