Hi, I need some help.
I want to join 3 tables.
Users, sports and a cross table.
One user can have many sports and I want to search all users by sportsname.
First table is users with
ID INT,
name varchar(),
prename varchar(),
Second table is sports with
ID INT,
sportsname varchar(),
third table is user_sports
ID INT,
users_ID INT,
sports_ID INT,
Waht I want to do is to find all users by searching a sportsname.
Example:
I want to find all users who likes to play golf.
I SQL it will look like:
SELECT users.id, users.name, sports.sportsname
FROM users
INNER JOIN users_sports
on users.id = users_sports.users_id
INNER join sports
on sports.id = users_sports.sports_id
Where sports.sportsname = 'Golf';
But I don’t know how to realize it in cakephp.
I create a own finder method in my UserTable.php
public function findSportsman(SelectQuery $query, $sports)
{
$columns = ['Users.*'];
$query = $query
->select($columns)
->distinct($columns);
$query->innerJoin('users_sports');
$query->innerJoin('sports');
}
I am not sure how to set the field refering to the inner join, in SQL it is on column.a = column.b
Or maybe there is another way to use such crossing tables.
Thanks