title: How to write this query (containing group by and join in subquery) in Laravel?
body: <p>I have two models Channel and Program. The relationship is : A Channel has many Programs.</p>
  
  <p>Now I intend to show how many programs a specific channel has in my view.</p>
  
  <p>So I come up with the following SQL to fetch relevant data.</p>
  
  <p><code>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;</code></p>
  
  <p>How to write this query in Laravel Eloquent?</p>
  
  <p>By the way, are there better,more efficient SQL to accomplish my task?</p>
  
  <p>Thank you in advance!</p>