left join with subqueries in laravel

Question

How can i do to reproduce this sql statement below with query builder or eloquent? I've tried using DB::raw... and join('something', function ($join)... but it is not working. If anyone knows how to figure this out, please show me an example.

SELECT
musica.titulo,
p.qtd_pedidos,
a.qtd_avaliacoes,
a.media_avaliacoes
FROM
musica
LEFT JOIN (
    SELECT musica_id, COUNT(pedido.id) as qtd_pedidos
    FROM pedido GROUP BY pedido.musica_id
) as p ON p.musica_id = musica.id
LEFT JOIN (
    SELECT musica_id, COUNT(avaliacao.id) as qtd_avaliacoes,
    ROUND(AVG(avaliacao.nota),1) as media_avaliacoes
    FROM avaliacao GROUP BY avaliacao.musica_id
) as a ON a.musica_id = musica.id

user2617846 user2617846 · Accepted Answer · 2013-07-25T08:48:28

Assuming you have described Musica, Pedido and Avaliacao models with their relations:

Musica::with(
    array('pedido' => function($query) {
              $query->select(DB::raw('musica_id, COUNT(pedido.id) as qtd_pedidos'))
                    ->groupBy('musica_id');
          },
          'avaliacao' => function($query) {
              $query->select(DB::raw(
                                'musica_id, '
                              . 'COUNT(avaliacao.id) as qtd_avaliacoes, '
                              . 'ROUND(AVG(avaliacao.nota),1) as media_avaliacoes'
                            ))
                    ->groupBy('musica_id');
          }
    )
)->get();

left join with subqueries in laravel

1 Answers