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

Hello

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 post.id = 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…

Hi!

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.

Regards

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 = post.id WHERE tag.tag_text = 'banana' OR tag.tag_text = 'ice';

regards,

webin

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 post.id = 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 = 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