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.
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?