Cakephp joining tables via find option not working

Question

I'm trying to join a Users table to my curent hasMany Through table which has Interest and User model id's.

Below is the find query with options:

$params = array(
                    'fields' => array('*', 'COUNT(DISTINCT(InterestsUser.interest_id)) as interest_count'),
                    'limit' => 15,
                    'recursive' => -1,
                    'offset' => $offset,
                    'conditions' => array('InterestsUser.interest_id' => $conditions),
                    'group' => array('InterestsUser.user_id'),
                    'order' => array('interest_count DESC', 'InterestsUser.user_id ASC', 'InterestsUser.interest_id ASC'),
                        'joins' => array(
                            array('table' => 'users',
                                'alias' => 'User',
                                'type' => 'LEFT',
                                'conditions' => array(
                                    'User.id' => 'InterestsUser.user_id',
                                )
                            )
                        )
                    );

            $results = $this->InterestsUser->find('all', $params);

This returns InterestsUser table fine but does not return any values for Users table. It only returns field names.

What could be wrong?

UPDATE: OK, above is generating below SQL which I got from Cake's datasources sql log:

SELECT *, COUNT(DISTINCT(InterestsUser.interest_id)) as interest_count 
FROM `interests_users` AS `InterestsUser` 
LEFT JOIN users AS `User` ON (`User`.`id` = 'InterestsUser.user_id')  
WHERE `InterestsUser`.`interest_id` IN (3, 2, 1)  
GROUP BY `InterestsUser`.`user_id`  
ORDER BY `interest_count` DESC, `InterestsUser`.`user_id` ASC, `InterestsUser`.`interest_id` ASC  
LIMIT 15

Why is users table values returning NULL only for all fields?

UPDATE:

OK I tried below but this is working fine...What am I missing here!!??

SELECT * , COUNT( DISTINCT (
interests_users.interest_id
) ) AS interest_count
FROM interests_users
LEFT JOIN users ON ( users.id = interests_users.user_id ) 
WHERE interests_users.interest_id
IN ( 1, 2, 3 ) 
GROUP BY interests_users.user_id
ORDER BY interest_count DESC 
LIMIT 15

ok I found the issue. It's this InterestsUser.user_id field!!! Apparently I need to take out ' to get this working! But why is this happening? How can I take this off as it is added by Cake's datasource? I really do not want to use custom sql query... — Passionate Engineer
Have you tried conditions => array('User.id = InterestsUser.user_id') ? — Doug Owings
Please look above at the initial Cake options query. It already has that value under joins array! — Passionate Engineer
oK I think this is MySQL's bug. Why do we have to take out ' on the either foreign key or primary key of joining tables to get this working? This is really strange... — Passionate Engineer
It's the difference between array('User.id' => 'InterestsUser.user_id') and array('User.id = InterestsUser.user_id') The docs have it like the latter http://book.cakephp.org/view/1047/Joining-tables. — Doug Owings

Doug Owings Doug Owings · Accepted Answer · 2011-10-29T09:25:07

The array syntax for join conditions should be like the following

array('User.id = InterestsUser.user_id')

as opposed to array('User.id' => 'InterestsUser.user_id'). For more, see http://book.cakephp.org/view/1047/Joining-tables.

Cakephp joining tables via find option not working

1 Answers