3. Querying in QCubed / * Picking database columns for QQuery

Picking database columns for QQuery

Most of the time, QQuery selects all the columns from the table and thus populates all the properties of the resulting objects. Normally, this is the right thing to do - the most expensive part of a typical query is hitting the database and performing the query;

However, when some tables have a large number of columns, or some columns that contain large objects (BLOB, TEXT, etc.), this may become expensive, both in terms of the traffic generated between application and database, and in terms of the memory footprint of the application.

Also, more and more databases are preventing you from creating SQL queries that might produce ambiguous results when using aggregate clauses. For example, if you create a query that groups employees by last name, and counts how many employees have each last name, but then also tries to select a first name, if there are mulitple employees with the same last name, the database will be confused and won't know which first name to show. Most databases will error in this situation. However, it would be perfectly fine to select a last name, because each group has the same last name. You need a way to specify particular database fields to select.

QQ::select solves this problem by allowing you to pick particular columns to fetch from the database.

QQ::Select can be passed as a clause to any query method. As shown in the second example below, it can also be passed as an argument to QQ::expand() to pick specific columns to fetch for the object to be expanded.

Note, that when QQ::Select is used, by default the primary keys are automatically added to the select list. This is illustrated by the first example below, where QQN::person()->Id is not part of the QQ::Select list, but $objPerson->Id is populated and used afterwards. This behaviour can be changed by using using the SetSkipPrimaryKey() method of QQSelect, as shown in the second example. This is typically useful for simple queries with the distict clause, where the presence of the primary keys would prevent distinct from having the desired effect

One QQ::select() can be used to select multiple columns, as shown in the fourth example below:

QQ::select(QQN::person()->Address->Street, QQN::person()->Address->City)

The same example also shows the use of QQ::select() in QQ::expandAsArray().

You may also notice, that many times the QQ::Select clause is passed as the last argument to the query method. Even though this is not ideal (since in SQL the select clause is the first in a statement), it was necessary for backward compatibility reasons with older versions of QCubed.

1. Get the first names of all the people

2. Get all the distinct first names of all the people

3. Get the last names of all the people, and the amount spent on the project they manage (if any), for Projects that have 'ACME' or 'HR' in it. Sort the result by Last Name, then First Name

Notice how some people may be listed twice, if they manage more than one project.

4. Projects and Addresses for each Person

PROFILING INFORMATION FOR DATABASE CONNECTION #1: 5 queries performed. Please click here to view profiling detail