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

score 2 · Accepted Answer · answered Nov 03 '14 at 14:18

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

Thanks for the answer. That's the same solution I went for in the end. It was a disappointing one but worth the single query. I realize they mention that the query function would come into play for complex queries.. I however don't believe this is complex. Last, I'd love to vote your answer up but I don't have enough rep >. — biko, Nov 04 '14 at 23:39

score 2 · Answer 2 · answered Feb 25 '15 at 12:51

I submitted a pull request that tries to fix this: https://github.com/catfan/Medoo/pull/206

What I did was to change medoo.php lines 564-568

// For ['column1' => 'column2']
else
{
    $relation = 'ON ' . $table . '."' . key($relation) . '" = "' . (isset($match[5]) ? $match[5] : $match[3]) . '"."' . current($relation) . '"';
}

to

// For ['column1' => 'column2']
else
{
    $and_relation = 'ON ' . $table . '."' . key($relation) . '" = "' . (isset($match[5]) ? $match[5] : $match[3]) . '"."' . current($relation) . '"';
    // if there is more than one condition
    if (count($relation) > 1) {

        // remove the "ON" part
        array_shift($relation);

        // add the condition to the join
        foreach ($relation as $key => $value) {
            // if the [$] value modifier is present
            if (preg_match('/\[\${1}\]/', $key) === 1) {
                $key = preg_replace('/\[\${1}\]/', '', $key);
                $and_relation .= ' AND "' . (isset($match[5]) ? $match[5] : $match[3]) . '"."' . $key . '" = ' . $value;
                continue;
            }
            // add additional table_relation
            $and_relation .= ' AND ' . $table . '."' . $key . '" = "' . (isset($match[5]) ? $match[5] : $match[3]) . '"."' . $value . '"';
        }
    }

    // write our new relation back
    $relation = $and_relation;
}

Note:

If the addition is a value with the joined table use the modifier [$]: 'right_table_column[$]' => value. If the value is a string you'll have to quote it before passing it the the join array, e.g.

// $db = Medoo Object
$value = $db->quote('myString');

$db->select(['table',
    [
        '[>]right_table' => [
            'join_condition' => 'join_condition', // ON
            'additional_condition[$]' => $value, // AND
            ],
    ],
    [
        ...where conditions...
    ]
]);

If it is a table relation use it as any other join condition 'left_table_column' => 'right_table_column'

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

2 Answers2