EXISTS support...? about squel HOT 8 CLOSED

I imagine the query within the exists can be constructed using squel.select() builder. Then you could just use .where('exists(' + existsQuery + ')') in the main query.

As for implementing the EXISTS keyword as a first-class item the way I would do is to implement a WhereExists block which exposed a .where_exists method. This block would be very similar to the Where block but would be specifically for the syntax you've described. So it would take a single parameter (a QueryBuilder instance). If .where_exists got called more than once then it would combine the multiple EXISTS invocations using the AND keyword as you've shown in your example query above.

What do you think?

from squel.

Let me check thanks

from squel.

+1 for exists.

from squel.

@hiddentao does it really make sense to use a separate WhereExists block? It seems like you'd want the option to mix field criteria with subqueries, e.g. WHERE (a.deleted_at IS NULL) AND (EXISTS (SELECT sub.id FROM ...)). I suppose they could share a @wheres but otherwise it seems like you'd have to worry about getting a single WHERE and would loose the ability to do an OR (EXISTS (...))

from squel.

Good point. And yet in that case the Where block already allows you to supply an arbitrary string so you could do something like this:

...
  .where('a.deleted is NULL')
  .where('EXISTS(' + squel.select().from('blah') + ')')
.toString()

Is this good enough?

from squel.

Am closing this due to lack of interest. I think the present solution (as outlined in the last comment) suffices.

from squel.

@hiddentao Sorry to resurrect after so long. If I want to use data from the user inside my EXISTS subquery, is there a way I can parameterize that and join it with the rest of my parameterized query to help me avoid injection attacks? (My EXISTS will be in a .field().)

from squel.

@raddevon Squel 5 has a more flexible architecture which allows for unlimited parameterized query nesting, so with the latest squel (5.4.2) you should be able to do something like:

squel.select()
  .from('table')
  .where('b = ?', 4)
  .where(squel.str('EXISTS(?)', squel.select().from('blah').where('a = ?', 5)))
.toParam()

/*
{ 
    text: "SELECT * FROM table WHERE (b = ?) AND (EXISTS((SELECT * FROM blah WHERE (a = ?))))", 
    values: [4,5]
}
*/

from squel.