Laravel raw query and including model relations

eloquent, laravel, mysql, php

Solution

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();

Problem

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]; ```

Original source