Medoo PHP framework - 'select' 'left join' using 'and' to test against a condition (as in col = val)

Question

I'm trying to make a left join select and use AND to compare a column against a specific value, i.e. a condition.

The problem is I don't know where to add the condition. The below medoo query returns the first shirt id of the same user id even if all 'is_wearing_shirt' set to 0. A regular mysql query however returns the expected data.

Here's the medoo query:

$db->select('users', array(
    '[>]shirts' => array(
        'user_id' => 'shirt_owner_id',
        'is_wearing_shirt' => 1 // equivalent to AND is_wearing_shirt = 1
    )
), array(
    'user_id',
    'shirt_id(shirt_id_being_worn)'
) , array(
    'user_id' => 1,
    'LIMIT' => 1
));
// always returns a shirt_id (even if all rows have is_wearing_shirt set to 0, in which case, shirt_id should be null)

Here's the regular MySQL query that works:

SELECT u.user_id, s.shirt_id
FROM  `cvs_users` u
LEFT JOIN `shirts` s
    ON user_id = shirt_owner_id
    AND shirt_being_worn = 1
WHERE user_id = 1
//returns the correct shirt_id

Answer 1

Concatenation of logical expressions using AND/OR in join clause is currently not supported by the medoo library (medoo 0.9.6.2). You can only use the query($query) method given by medoo to directly execute sql queries. For your example the query looks like this:

$data = $db->query("SELECT u.user_id, s.shirt_id FROM `cvs_users` u 
    LEFT JOIN `shirts` s 
    ON user_id = shirt_owner_id AND shirt_being_worn = 1 
    WHERE user_id = 1")->fetchAll();

Note the call to fetchAll() at the end to get the queried data. I ran into the same problem and debugging the medoo.php code revealed that AND/OR conditions are only considered within where clause. Maybe they'll put this feature in a future update. I have started an issue describing the problem: Medoo Issue