CODEWITHSUNDEEP
mysqllaravel
Carter
Carter

Reputation: 1260

How to write this query (containing group by and join in subquery) in Laravel?

I have two models Channel and Program. The relationship is : A Channel has many Programs.

Now I intend to show how many programs a specific channel has in my view.

So I come up with the following SQL to fetch relevant data.

SELECT channels.channel_id, channels.name, num_of_programs FROM channels LEFT JOIN ( SELECT p.channel_id cid, COUNT(*) num_of_programs FROM programs p, channels c WHERE p.channel_id = c.channel_id GROUP BY p.channel_id ) v ON channels.channel_id = v.cid;

How to write this query in Laravel Eloquent?

By the way, are there better,more efficient SQL to accomplish my task?

Thank you in advance!

Upvotes: 0

Views: 243

Answers (2)

Munees Majid
Munees Majid

Reputation: 767

You can accomplish this with the join and group by, and i am not familiar with Laravel Eloquent. You can try with this

    SELECT c.channel_id,c.name,COUNT(p.program_id)
    FROM channels c 
    LEFT JOIN  programs p ON p.channel_id = c.channel_id
    GROUP BY p.channel_id;

Upvotes: 1

fmgonzalez
fmgonzalez

Reputation: 823

There are so many possibilities. That's one: 

    DB::table('channels')
        ->join('programs', 'channels.id', '=', 'programs.channel_id')
        ->select('channels.id', 'channles.name', DB::raw('count(programs.id) as num_of_programs'))
        ->groupBy('channels.id', 'channels.name')
        ->get();

I hope it works fine for you.

Upvotes: 1

Related Questions