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')

score 1 · Answer 1 · answered Oct 16 '18 at 01:23

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).

Model: Apply condition on join statements

1 Answers1