I am trying to return a single post with a sub-query to include a count of down-votes and up-votes. I am able to achieve this by using a raw query.
However doing so means that I can no longer access the post models relations e.g. comments.
Is there more of an Eloquent way I can do this to keep the models relations?
Or is there a better way I could be going about the whole thing? Would love to hear how you would go about it.
$post = DB::select(
"SELECT post.*,
(SELECT COUNT(*) FROM posts_votes
WHERE type = 1
AND post_id = posts.id)
AS up_votes,
(SELECT COUNT(*) FROM posts_votes
WHERE type = 2
AND post_id = posts.id)
AS down_votes
FROM posts
INNER JOIN posts_votes ON posts.id = posts_votes.post_id
WHERE posts.id = ?", [$id])[0];
This is an old question, but it's the first result I get when I google "laravel relation raw query", so it might be useful for other people that are searching the same thing.
I managed to solve it like this:
$query = ""; //raw query goes here
$result = Model::fromQuery($query);
$result->load('relationship');
Make sure your custom raw query contains the proper columns specified in the model, otherwise the relationship will always return null.
Tested on Laravel 6.17.0
Try this
Post::select('*')
->addSelect(DB::raw('
(select count(*)
from posts_votes
where type = 1
and post_id = posts.id)
as up_votes
'))
->addSelect(DB::raw('
(select count(*)
from posts_votes
where type = 2
and post_id = posts.id)
as down_votes
'))
->whereIn('id', $ids)
->get();