question sqldriver class - how to do subqueries?

sprockett

I'm trying to figure out how to do subqueries and can't seem to figure out how, using the sqldriver class. I tried looking through existing examples in the, but did not find any using subqueries.

1, Can anyone post a quick example on how setting up a select with a subquery would work?

2. Is there a quick way to just manually input a full query string and execute that rather than have to build it using the various functions?

Thanks!

judgej

Could you post an example of the SQL you want to run? Is this sub-query in a WHERE clause (e.g. WHERE x IN (SELECT...)) or is it a table query (e.g. ... FROM (SELECT x FROM ...) AS subquery_table)

Thinking about it, I'm not even sure of MySQL does table subqueries (the second example).

Have you tried using a simple where clause but setting the 3rd or 4th parameter to FALSE to tell the query object not to parse the where condition in any way? It would be something like this (a scalar version):

->Where(MyColumn =', '(SELECT x FROM y WHERE z)', TRUE, FALSE)

or the row version:

->WhereIn('MyColumn', '(SELECT x FROM y WHERE z)', FALSE)

Something like that has worked for me where I have used MySQL [date] functions in the condition. If doing a correlated subquery (where the inner query references values from the outer query) make sure you have table aliases set and use those aliases in the inner query, since without the parsing I don't think the subquery table names will get expanded to include the database table prefix that you chose when installing.