Level 122
its an eloquent function
Aug 12, 2022
4
Level 12
WhereHas with DB Query Builder
Hey!
I need to make this search functionality also search through tags.
\DB::table('posts')
->where(function($query) {
$query->orWhere('published_at', '<', now())
->orWhereNull('published_at');
})
->where('moderated', \DB::raw('true'))
->where(function($query) use ($request) {
$query->orWhereRaw('LOWER(title) LIKE LOWER(?)', ["%{$request->q}%"])
->orWhereRaw('LOWER(content) LIKE LOWER(?)', ["%{$request->q}%"]);
})
->join('categories', 'posts.category_id', '=', 'categories.id')
->join('users', 'posts.user_id', '=', 'users.id')
->select('posts.*', 'categories.name as category_name', 'categories.slug as category_slug', 'users.name as author_name')
->orderByDESC('posts.created_at')
->paginate(6)
]);
The tags of the posts are attached with a pivot table. How can I make this work without switching to Eloquent and add it in the query above?
...
->whereHas('tags', function ($query) use ($request) {
return $query->where(function($query) use ($request) {
$query->orWhere('tags.name', $request->q)
->orWhere('tags.slug', $request->q);
});
})
...
Level 12
@Snapey Can you show me some example or give me any suggestions If I can build an additional condition which would work just like the whereHas() method inside my current DB Query?
Level 12
@Snapey Anyways, I'll just simply switch to Eloquent. What's wrong with this one because I'm not getting correct results?
Post::with('author', 'category', 'tags')
->where(function($query) {
$query->orWhere('published_at', '<', now())
->orWhereNull('published_at');
})
->where('moderated', \DB::raw('true'))
->where(function($query) use ($request) {
$query->orWhereRaw('LOWER(title) LIKE LOWER(?)', ["%{$request->q}%"])
->orWhereRaw('LOWER(content) LIKE LOWER(?)', ["%{$request->q}%"])
// LOOK HERE
->orWhereHas('tags', function ($query) use ($request) {
return $query->where(function($query) use ($request) {
$query->orWhere('tags.name', $request->q)
->orWhere('tags.slug', strtolower($request->q));
});
});
})
->orderByDESC('created_at')
->paginate(6)
Level 122
@Laralex whereHas is quite slow. If you can, switch to a join, or query the has relation and then use whereIn in your main query
Please or to participate in this conversation.