Level 2
Remember to remove spaces from each element in your $meta_keywords array. Just run trim(). Not sure if that's the problem here but it could definitively affect your results.
May 22, 2019
8
Level 1
Using whereIn and comma separated values
Hi everyone. I am building a related posts block. In the posts table, I have a column called meta_keywords, which holds a string with comma separated values:
meta_keywords = "earth, wind, fire"
I want to get related posts, based on the meta_keywords column.
My way of going at this is as follows:
//get the meta_keywords from a single post
$meta_keywords = explode(',',$post->meta_keywords);
//get posts that match the criteria plus have some of the meta_keywords
$relatedPosts = Post::where('category_id','=',$post->category_id)
->where('status','=','PUBLISHED')
->where('id','!=',$post->id)
->whereIn('meta_keywords',$meta_keywords)
->inRandomOrder()
->take(3)->get();
Other posts in the same category, have the following meta_keywords:
"wood, stone, earth"
"water, fire, earth"
"water, wood, grass"
However I am not getting any results for this query. Can anyone please help me? Thanks!
Level 28
whereIn() is not going to work like that. Here's a better approach. Set up a keywords table and a pivot table for keyword_post. Then set up a manyToMany relationship a post hasMany Keyword and keywords hasMany Post. For related posts you would use the pivot table to query. This approach will allow the database to do the heavy lifting with the added benefit of not having duplicated keywords for posts.
Tables:
posts
id
status
keywords
id
name
keyword_post
id
keyword_id
post_id
Level 122
Storing multiple values in a column is not a good idea for this reason.
Suggest you consider a tagging package?
1 like
Level 1
Thanks so much for all the answers, I really appreciate your points of view.
What happens is that I am using Laravel Voyager as an admin, and the meta_keywords column within the POST table handles it like this, which I agree it isn't the best approach.
However time is pressing, so I am trying to find a workaround to this constrain.
If anyone can come up with another idea, I'm all ears. I will try removing white spaces and see if it helps. Thanks!
Level 122
so if you want to find 'wind' in a field containing 'earth, wind, fire' then you will need to use a like search with % on either side
but, it will also find partial matches, so pulling also a row containing 'brass, woodwind, drums'
Once you have your results you can filter more acurately with regex
Level 122
$terms = explode(',',$post->meta_keywords);
$query = Post::where('category_id','=',$post->category_id)
->where('status','=','PUBLISHED')
->where('id','!=',$post->id)
->where(function($query) use($terms) {
foreach($terms as $term) {
$query->orWhere('meta_keywords', 'like', "%$term%");
};
})
->inRandomOrder()
->take(3)
->get();
1 like
Level 1
@snapey Thank you so much! This is exactly what I was looking for. I had tried using like, but in a wrong way, your solution is perfect. And yes, I'll use regex afterwords to fine tune the result. Thanks again :)
Level 8
Check FIND_IN_SET()
https://www.w3schools.com/sql/func_mysql_find_in_set.asp
And for relations check my package for Laravel Eloquent Models that can create relationships from comma separated ID's or JSON or Serialized
Please or to participate in this conversation.