Trying to make a query based on related (one_to_may) strings


I am trying to create a query, where i need to check for a string match on a related object, but I cannot get this to work as I want.

Model1: Post. Has property name, id, and text

Model2: Tags: Has property post_id and tag_text. Post_id is a foreign key, pointing to Post->id

I am trying to make a query, where i am searching for something like "banana". If i get a match on Tags->tag_text on banana, return the Post object.

If I am searching for "banana" and "ice", return a Post model with both "banana" and "ice" as related Tag-objects…

I am doing some raw sql for testing, and it looks like this:

SELECT * FROM post INNER JOIN tag on = tag.post_id WHERE tag.tag_text = ‘banana’ AND tag.tag_text = ‘ice’;

The problem is that I am not getting any results, even if I know that there are one Post with two related Tag-objects containing the tag_text described above…


Shouldn’t this:

be this?

Currently you are using "AND" which means:

Show all posts where tags are "banana AND ice"…

But show all posts where tags are “banana OR ice” would make more sense when I read your description obove. :)


Well, I only want the result where both "banana" and "ice" are related to the post object

my dear you have to use OR inplace of AND

SELECT * FROM `tag` INNER JOIN `post` on tag.post_id = WHERE tag.tag_text = 'banana' OR tag.tag_text = 'ice';



If you are looking for posts tagged ‘banana’ OR ‘ice’ MetaCrawler’s code is ok. But if you want to get ‘banana’ AND ‘ice’ use this (hope I’m not messing here):

SELECT *, COUNT(post_id) AS c FROM `post` INNER JOIN `tag` on = tag.post_id WHERE tag.tag_text = 'banana' OR tag.tag_text = 'ice' GROUP BY tag.post_id HAVING c = 2;

Ok, but in that case I am getting all the Post objects containing only ‘banana’ and only ‘ice’, and not the one that contains both by itself?

if you have data like this: banana, ice then u can try below

SELECT * FROM `tag` INNER JOIN `post` on tag.post_id = WHERE tag.tag_text = 'banana' OR tag.tag_text = 'ice' or tag.tag_text = 'banana, ice' ;

They are in separate tag-rows

then use OR

But that will not give me the post that contains both ‘banana’ and ‘ice’ by itself

Great! It works like a charm. However, the query takes quite a long time to execute…

Yes, it’s slower. You can optimize it by using proper indexes though.

Maybe someone knows better way? It would be great…

Yes, adding indexes did help a little. Thanks

Now, the million dollar question: how can i make this query a yii2 query based on a query-object?

Try more or less this

Post::find()->select('*, COUNT(post_id) AS c')->joinWith(['tags'])->where(['or', ['tag_text' => 'banana'], ['tag_text' => 'ice']])->groupBy('post_id')->having(['c' => 2]);

Perfect! Thanks