Joins

You can use custom joins to narrow down which records are returned, even for relationships that are not defined in your mapping definitions.

The query builder can also be used to specify custom joins with aliases that can be used in the criteria. Normally, joins are handled automatically based on the relationships you define between your entities, however, sometimes you will want to limit the records that are returned by adding a custom join (perhaps back to the same entity), and specifying criteria based on any joined records. To do this, just call the leftJoin or innerJoin method on the query builder like this:

$query = QueryBuilder::create()
    ->leftJoin(Policy::class, 'p2')
    ->on('p2.policyNo', '=', 'policyNo')
        ->and('p2.id', '>', 'id')
        ->and('p2.status', '=', Policy::STATUS_INFORCE)
        ->and('p2.modification', '=', 'CANCELLED')
    ->where('modification', QB::NOT_EQ, 'CANCELLED')
    ->and('p2.id', 'IS', null)
    ->buildSelectQuery();

The above would be translated into SQL such as this:

SELECT [...] FROM `policy` 
LEFT JOIN `policy` `p2` ON `p2`.`policy_no` = `policy`.`policy_no`
    AND `p2`.`id` > `policy`.`id`
    AND `p2`.`status` = 'INFORCE'
    AND `p2`.`modification` = 'CANCELLED' 
WHERE `policy`.`modification` != 'CANCELLED'
    AND `p2`.`id` IS NULL