ASK CActiveRecord Get Id Who Never Used in Relation Table


I have few tables look like this:

Blog    BlogArticle  Article

----    -----------  -------


id------blog_id     -id

title   article_id_/ title

To be clear, the relation is: HAS_MANY BlogArticle.blog_id HAS_MANY BlogArticle.article_id

My question is, may i get that never been linked to without use WHERE NOT IN clause? because i think it (WHERE NOT IN) will impact database server performace when we use it for a really big list (cmiiw)

Thanks in advance.

Just left join Article to BlogArticle and apply a where clause that predicates that IS NULL.

SELECT id, title FROM Article LEFT JOIN BlogArticle ON = BlogArticle.article_id WHERE IS NULL;

Hi Luke,

Many thanks for your help, but i mean i want to look with specific blog_id.

Thanks in advance

That first error:

Is because you are selecting id instead of, it doesn’t know which id you want.

With the AR question, I’m not sure, you could instead move the condition into the has many, for example:

'notBlogged' => array(self::HAS_MANY, 'Blogs', 'condition' => ' IS NULL') 

$article = Article::model()->with('notBlogged')->find();

Not 100% sure this will work, I can’t test it here at the moment but this is a reusable way.

I’m sorry for my english, but i mean with specified blog_id, so lemme explain, in example, i have 2 blogs and 7 articles rows, each blog shouldn’t have duplicate article but allowed to have same articles against others.

i just afraid if my english cant tell you what i mean so this is the SQL if use WHERE NOT IN


FROM  `article` 

WHERE  `article`.`id` NOT IN (

        SELECT CONCAT(  `blog_article`.`article_id` ) 

        FROM  `blog_article` 

        WHERE  `blog_article`.`blog_id` =1


Many Thanks.

Okay i get it

I don’t know if this “the proper way” or not.

first i describe the Article relation like this one

public function relations()


	return array(

		'unused' => array(self::HAS_MANY, 'BlogArticle', 'article_id',

			'joinType' => 'LEFT JOIN',

			'on' => 'CONCAT(unused.article_id) = AND unused.blog_id = :blog_id'),



and use it

$article = Article::model()->with(array(

	'unused' => array(

		'params' => array(

			':blog_id' => 2



))->find('unused.article_id IS NULL');

Yeah that’s how I’d do it and if you take a look around I would say that the consensus is that the way you’ve done it is the “proper way”, sorry I wasn’t more helpful I wasn’t completely clear on what you wanted to do. Not sure I understand why you are performing a string concatenation on that id however.