So, I have an table ‘article’ that is related to the table ‘player’ and connected by the table ‘player_article’. I want to find articles related to my current article based on being related to the same players. I’ve done some research and know there are problems querying MANY_MANY and HAS_MANY relations using LIMIT. I need to use ‘together’ so that I can include my condition for the related players in the query, but then my LIMIT isn’t hit.
See here: http://www.yiiframework.com/forum/index.php/topic/10597-limit-working-with-many-many-relations/
It is explained here as well: http://www.yiiframework.com/doc/guide/1.1/en/database.arr#relational-query-performance
In my case, I don’t need any of the data on the related models, I just need to use them in the query as a condition. I was just looking for an answer to this and it looks like I was able to solve my problem in pure MySQL by adding the DISTINCT keyword on the id field for the article table.
This is my code, it’s correctly returning 7 articles:
$criteria = new CDbCriteria;
$criteria->distinct = true; // --- Only return distinct articles
$criteria->with = array(
'commentCount',
'playerArticles'=>array(
'select'=>false, // --- We don't want to load those related models
'together'=>true, // --- Must include this so we can query based on the related players
),
);
$criteria->compare('active',1);
$criteria->limit = 7;
$criteria->order = '`t`.`publish_time` DESC';
Does anyone see any issue with this?