Converting SQL query to an Eloquent query builder.

Hello, I am pretty new to Laravel, and can't find a solution to my problem. I've made a SQL query,

$likes =    DB::select("
                SELECT count(likes.post_id) AS AllCount, posts.id, posts.image
                FROM posts, likes
                WHERE likes.post_id = posts.id
                GROUP BY likes.post_id, posts.id, posts.image
                ORDER BY AllCount DESC
                ");

It works like charm, but I ran in to a problem of pagination, since I can't just add ->paginate(3); at the very end, and the solution is to use Eloquent query builder. I was trying to convert this code, but I was just not able to. My try is:

$likes = DB::table('posts', 'likes')
            ->select(DB::raw('count(likes.post_id) as AllCount, posts.id, posts.image'))
            ->from('posts', 'likes')
            ->whereRaw('likes.post_id = posts.id')
            ->groupBy('likes.post_id', 'posts.id', 'posts.image')
            ->orderBy('AllCount', 'DESC')
            ->paginate(3);

but all it shows for me, is:

SQLSTATE[42S22]: Column not found: 1054 Unknown column 'likes.post_id' in 'where clause' (SQL: select count(*) as aggregate from `posts` where likes.post_id = posts.id group by `likes`.`post_id`, `posts`.`id`, `posts`.`image`)

bipin

8 years ago

Level 2

try this

  $likes = DB::table('posts')
              ->join('likes','likes.post_id','=','posts.id')
             ->select(DB::raw('count(likes.post_id) as AllCount', 'posts.id', 'posts.image'))
            ->groupBy('what you like to group put here')
            ->orderBy('AllCount', 'DESC')
            ->paginate(3);

pasiutlige

8 years ago

Level 1

Hmm, before this to display information in view, I used

@foreach($likes as $like)
        <div class="card col-md-4 col-sm-6 co-xs-6 gal-item ">
            <div class="">
                <a href="/posts/{{$like->id}}"><img src="/storage/images/{{$like->image}}" class="img-thumbnail img-responsive hover" alt=""></a>
                {{--<h3><a href="/posts/{{$post->id}}">{{$post->title}}</a></h3>--}}
                <small>Uploaded by {{$like->name}}</small>
            </div>
        </div>
    @endforeach

Would this same code work if I adapted your code correctly? Or will I have to adapt this one too? I am kinda lost at the moment, not sure where I am doing wrong.

pasiutlige

8 years ago

Best Answer

Level 1

So after messing around I finally got it work, the answer is:

$likes = Post::
            select(DB::raw('count(likes.post_id) as AllCount, posts.id, posts.image, posts.user_id, users.name, posts.title'))
            ->leftJoin('users', 'users.id', '=', 'posts.user_id')
            ->leftJoin('likes', 'likes.post_id','=','posts.id')
            ->groupBy('likes.post_id')
            ->orderBy('AllCount', 'DESC')
            ->take(3)
            ->get();

You should notice the code changed from the very start, since I got one more table to join, but in general, that's how it was supposed to be.

Please or to participate in this conversation.