ASK CActiveRecord Get Id Who Never Used in Relation Table

pengemizt · November 6, 2011, 9:49am

Hi,

I have few tables look like this:




Blog    BlogArticle  Article

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

        id

id------blog_id     -id

title   article_id_/ title

To be clear, the relation is:

Blog.id HAS_MANY BlogArticle.blog_id

Article.id HAS_MANY BlogArticle.article_id

My question is, may i get Article.id that never been linked to Blog.id 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.

l.jurgs · November 6, 2011, 10:06am

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




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

pengemizt · November 6, 2011, 12:09pm

Hi Luke,

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

Thanks in advance

l.jurgs · November 6, 2011, 1:01pm

Niko Wicaksono:

Hi Luke,

It was works well, very well, but I’m just tired to implement this to AR Class with this command
$article = Yii::app()->db->createCommand()

			->select('id')

			->from('article')

			->leftJoin('blog_article', 'article.id = blog_article.article_id')

			->where('blog_article.id IS NULL')

			->queryRow(); // update, i miss this line
the error msg say "Property "CDbCommand.id" is not defined.". Its appears when i try to print $article->id.

the error msg say “SQLSTATE[23000]: Integrity constraint violation: 1052 Column ‘id’ in field list is ambiguous.”

The only way i know was working is create a relation call (example) "blogs" with HAS_MANY relation in Article AR Class pointing to ArticleBlog AR Class and call it like this
$article = Article::model()->with(array(

	'blogs' => array(

		'condition' => 'blogs.id IS NULL'

	)

))->find();
My question is, do you have "the right way" to implement this query with Yii AR Class?

Thanks in advance.

That first error:

Is because you are selecting id instead of Article.id, 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' => 'Blogs.id 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.

pengemizt · November 6, 2011, 1:43pm

Luke Jurgs:

That first error:

Is because you are selecting id instead of Article.id, 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' => 'blogs.id 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


SELECT * 

FROM  `article` 

WHERE  `article`.`id` NOT IN (

        SELECT CONCAT(  `blog_article`.`article_id` ) 

        FROM  `blog_article` 

        WHERE  `blog_article`.`blog_id` =1

)

Many Thanks.

pengemizt · November 6, 2011, 4:38pm

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) = t.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');

l.jurgs · November 6, 2011, 11:12pm

Niko Wicaksono:

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) = t.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.