Yii Criteria with "with" yields duplicates

Question

This is my Criteria:

$criteria = new CDbCriteria();
$criteria->with = array('userUrls');
$criteria->together = true;
$criteria->compare('userUrls.community_id',Yii::app()->params['currentCommunity']->id);
$criteria->order = 't.weight DESC, t.id DESC';
$urls = Url::model()->findAll($criteria);

I am basically trying to do a simple JOIN via a MANY_MANY. Except in the JOIN table I need an additional condition (that the community_id matched the given one).

Without $criteria->together = true; it fails in the WHERE statement.

If I add $criteria->distinct = true; it still gives me duplicates because other fields in the JOIN table make them technically 'UNIQUE'.

I want the results to be UNIQUE based on the URL fields, not the userUrls fields.

I think I found a very simple fix: `GROUP BY`! – Nathan H Nov 15 '12 at 18:15 — Nathan H, Nov 15 '12 at 18:15

score 2 · Accepted Answer · answered Nov 18 '12 at 13:58

2

Adding $criteria->group = 't.id'; fixed it all. Which makes sense.

answered Nov 18 '12 at 13:58

Nathan H

48,033
60
165
247

score 1 · Answer 2 · answered Nov 15 '12 at 18:04

1

try altering the join clause.

$criteria->join = 'LEFT JOIN';

answered Nov 15 '12 at 18:04

jarchuleta

1,231
8
10

CDbCommand failed to execute the SQL statement: SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'LEFT OUTER JOIN `user_url` `userUrls` ON (`userUrls`.`url_id`=`t`.`id`) WHERE (' at line 1 – Nathan H Nov 15 '12 at 18:11
I think it tried to JOIN twice with this: SELECT `t`.`id` AS `t0_c0`, `t`.`url` AS `t0_c1`, `t`.`urlHash` AS `t0_c2`, `t`.`language` AS `t0_c3`, `t`.`title` AS `t0_c4`, `t`.`summary` AS `t0_c5`, `t`.`parent_id` AS `t0_c6`, `t`.`views` AS `t0_c7`, `t`.`mainImage` AS `t0_c8`, `t`.`weight` AS `t0_c9`, `userUrls`.`id` AS `t1_c0`, `userUrls`.`user_id` AS `t1_c1`, `userUrls`.`url_id` AS `t1_c2`, `userUrls`.`community_id` AS `t1_c3`, `userUrls`.`create_time` AS `t1_c4` FROM `url` `t` LEFT JOIN LEFT OUTER JOIN `user_url` `userUrls` ON (`userUrls`.`url_id`=`t`.`id`) WHERE (userUrls.community_id=:ycp0) – Nathan H Nov 15 '12 at 18:12
try adding the join and removing together. – jarchuleta Nov 15 '12 at 20:38

Yii Criteria with "with" yields duplicates

2 Answers2