Select Queries

For improved performance, you can partially hydrate entities using a custom select query. You can also use a custom select query to load a different entity than the one assigned to the repository.

The query builder supports select and from methods in a similar way to a plain SQL query (but usually dealing with classes/properties, not tables/columns) - these methods are described below. However, in most cases, you will build select queries without calling the select or from methods, just specifying criteria (and possibly joins), eg:

$query = QueryBuilder::create()
    ->where('lastName', '=', 'Smith')
    ->orderBy('firstName')
    ->buildSelectQuery();    

The above will fully populate all properties of the entity that is already associated with the repository - you do not need to specify what to select in the query, as by default, queries will return everything. In cases where you only want to hydrate certain properties though, you can specify which ones to return using the select method:

$query = QueryBuilder::create()
    ->select('firstName', 'lastName')
    ->buildSelectQuery();

The above query will just return the properties firstName and lastName of whatever entity class is specified on the repository. As no criteria has been specified, it will return that data for ALL records in your database (more about specifying criteria later). If you are not binding to entities, and just want to return scalar values (eg. using the findValuesBy method, or setting the bindToEntities config option to false), you can also specify SQL functions, columns, and expressions as well as properties as shown below (using table and column names is not recommended in most cases though, as it tightly couples your database design to your code):

$query = QueryBuilder::create()
    ->select('CONCAT_WS(' ', `title`, %firstName%, %lastName%)')
    ->buildSelectQuery();

The above query uses the CONCAT_WS MySQL function, with the backtick delimiter indicating that title is the literal name of a column in the table, and the percent delimiters indicating that firstName and lastName are the names of properties on the object.

You can also be explicit about which class to return the properties of (not necessary unless you have not told the repository which entity class it is dealing with, or if you want to load from a different class than the one the repository is set to use):

$query = QueryBuilder::create()
    ->select('firstName', 'lastName')
    ->from(Contact::class)
    ->buildSelectQuery();

If your class has properties which join to child objects, you can also select to populate selected properties of the child object(s) using dot notation. So if your Contact class had a property named department which linked to a child object and you had already set up the mapping definition for that relationship, you could use a query like this to populate just the contact first name, last name, and the department name:

$query = QueryBuilder::create()
    ->select('firstName', 'lastName', 'department.name')
    ->from(Contact::class)
    ->buildSelectQuery();

Note that you do not need to specify any join information here, because that will already be specified in the mapping information. However, if you wanted to join to an entity for which there is no mapping information specified, you can also add custom joins to a query.

When you specify certain fields to return using the select method,