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…
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 = 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 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
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]);