How to properly construct a JOIN using Database class

Kirby 3 Solved ✅
1

i tried:

$result = Db::table($table)->select($columns)->join($joinType, $joinTable, $joinOn)->where($where)->all();

but it throws an exeption:

Invalid join type VIDEO_TO_CATEGORIES.VIDEO_ID = VIDEOS.VIDEO_ID

where the latter is the value of $joinOn, so it seems to be messing up my params but i can’t get behind it…

2

Hm, the error is thrown on type, so it seems the value you pass for $type is not correct. See line 453 kirby/src/Database/Sql.php

3

thanks for the quick reply! but i am passing the right values afaik:

 $table = 'videos';
 $columns = '*';
 $joinType = 'LEFT JOIN';
 $joinTable = 'video_to_categories';
 $joinOn = 'video_to_categories.video_id = videos.video_id';
 $where = "(video_to_categories.category_id = 2) AND videos.video_title LIKE '%" . $query. "%'";
 $result = Db::table($table)->select($columns)->join($joinType, $joinTable, $joinOn)->where($where)->all();

but the exception

Invalid join type VIDEO_TO_CATEGORIES.VIDEO_ID = VIDEOS.VIDEO_ID

indicates that the Vars have been mixed up somewhere

4

i found the solution, however it is pretty odd:

by passing the $joinType as last param (opposed to what the funtion definition sez) it works:

$results = Db::table($table)->select($columns)->join($joinTable, $joinOn, $joinType)->where($where)->all();

Function Definition:

$sql->join(string $type, string $table, string $on): array

(https://getkirby.com/docs/reference/@/classes/database/sql/join)

5

There is another join() method in the Query class with a different order of arguments, so I guess that’s the one that’s used here.

6

you are right, that explains it all, thanks!


however it would be nicer to have the order be the same for both functions :wink:
7

The database class is currently being rewritten. Perhaps such inconsistencies will also be cleaned up in the process.