Medoo PHP db framework - select join

Question

Using Medoo PHP database framework and trying to make a join

$users = $db->select(
    'users',
    [
        '[>] tournaments_users' =>
        [
            'tournaments_users.user_id' => 'users.uid'
        ]
    ], [
        'users.uid',
        'users.name',
        'users.modifier',
        'users.handicap',
        'tournaments_users.tournament_id'
    ], [
        'tournaments_users.tournament_id' => 1
        'ORDER' => 'users.username ASC'
    ]
);
foreach( $users as $u) {
    echo $u['name'].'<br>';
}

The selection results in an invalid argument supplied for foreach().

Removing 'tournaments_users.tournament_id' from the column- and where-section makes the query work, but does not show the correct data.

Why is the query invalid?

@oliakaoil: i get array(3) { [0]=> string(5) "42S22" [1]=> int(1054) [2]=> string(64) "Unknown column 'tournaments_users.tournament_id' in 'field list'" }. Should i build up the join in some other way? — 2mas, Jul 17 '14 at 18:35

score 0 · Accepted Answer · answered Jul 18 '14 at 07:08

0

Changing the join-selection to

        '[>]tournaments_users' => 
        [
            'uid' => 'user_id'
        ]

solved the issue with invalid argument.

answered Jul 18 '14 at 07:08

2mas

127
2
14

score 0 · Answer 2 · answered Sep 22 '15 at 10:11

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.

Medoo PHP db framework - select join

2 Answers2