Model: Apply condition on join statements

Question

I want to add some conditions on a model's join.

this is what I tried and nothing changed on the result:

    Members::find('all', [
        'fields' => ['name', 'Count(Orders.id)'],
        'with' => 'Orders',
        // 'joins' => [
        //  'Orders' => 'Orders.status = delivered'
        // ]
    ]);

this is my working example query:

SELECT name, COUNT(orders.id) FROM members
LEFT JOIN orders ON orders.member_id = members.id AND orders.status = 'delivered'
GROUP BY member_id

Edit:

I also tried this approach based on this answer

$joins = array();
$joins[] = new \lithium\data\model\Query(array(
     'source' => 'orders',
     'type' => 'LEFT',
     'constraint' => array('Orders.status' => 'delivered'),
));


Members::find('all', [
    'fields' => ['name', 'Count(Orders.id)'],
    'with' => 'Orders',
    'joins' => $joins
]);

but the created query doesn't contain defined joins parameters ('Orders.status' => 'delivered')

Answer 1

What you are trying to do is conceptually wrong. Please try to put the status = 'delivered' in the where clause of the query. For more info, read below:

Although the query you are writing makes sense when read, and (might) yield the same results, you are mixing the join clause and the where clause.

• Join clause should contain conditions that are required to "join" two records together (i.e. same name, same first_name and last_name, same make and model, etc.)

• Where clause should contain conditions that are necessary to return only those records that satisfy the conditions. These records can be from a single table (usually) or from multiple tables (in case of joins).