SQL Subqueries for QQuery

QQuery provides excellent means to perform many common queries; however, once in a while, you'll find yourself needing to do some custom SQL. Don't jump into full custom queries just yet. You can write that little piece of SQL as a part of your QQuery in many cases using SQL subqueries.

In the example below, we need to find the names of the project managers whose projects are running over budget. While it's possible to write this query in a straight-up QQuery, for this example, we'll use a SubSql mechanism instead just for illustration purposes.

Important gotcha: you have to define your subquery as a part of a Condition! To make it available in the returned array as a Virtual Attribute, you also have to put in a QQ::expand() clause to have the SELECT clause of the query include the subquery result.

Note: the code below generates correlated (dependent) subqueries. These are frequently not the fastest way to run queries against your SQL engine. If there is an opportunity to rewrite your subquery using simple joins, do it - this will improve the performance of your applications dramatically.

In general, it's a good idea to use EXPLAIN statements to determine the query execution plan of the SQL statement that QQuery generates to determine what the SQL engine will actually do to run your queries. This is one of the best ways to improve the performance of your database-driven application.

Select names of project managers whose projects are over budget by at least $20

Karen Wolfe: 2 project(s) over budget
John Doeooo: 1 project(s) over budget