我正在尝试将用户表连接到我当前的hasMany Through表,该表具有兴趣和用户模型id。
下面是带有选项的查找查询:
$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);
返回InterestsUser表,但不返回Users表的任何值。它只返回字段名。
怎么了?
更新:好的,上面是生成下面的SQL,我从蛋糕的数据源SQL日志:
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
为什么用户表值返回NULL只对所有字段?
更新:
好吧,我在下面试过了,但这工作得很好…我错过了什么?
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
连接条件的数组语法应该如下所示
array('User.id = InterestsUser.user_id')
相对于array('User.id' => 'InterestsUser.user_id')
。