CODEWITHSUNDEEP
phplaravellaravel-4
Ruben
Ruben

Reputation: 454

laravel 4 , AVG and COUNT inside a join query

I'm trying to execute a query with avg and count inside the rows result, something like that :

SELECT r.id, avg( p.puntuacio ), count(p.puntuacio)
FROM receptes AS r, puntuacio_receptes_usuaris AS p
WHERE r.id = p.recepta_id
GROUP BY r.id

But I don't know can I do on Laravel because in Eloquent can't write avg or count inside a result row.

Thanks a lot

Upvotes: 1

Views: 4056

Answers (2)

Muhammad Usman
Muhammad Usman

Reputation: 12503

Query Builder (Fluent) method:

DB::table(DB::raw('receptes as r, puntuacio_receptes_usuaris as p'))
        ->select(array('r.id', DB::raw('avg( p.puntuacio ) as avg_p'), DB::raw('count(p.puntuacio) as count_p')))
        ->where('r.id', DB::raw('p.recepta_id'))
        ->groupBy('r.id')
        ->get();

This should work without any problem, but if you want to do it with your Eloquent model, I recommend using JOIN instead of using two tables in FROM.

You can access the avg and count results by accessing avg_p and count_p.

Note:

  • DB::raw() instructs Laravel not to escape what's within, see the doc.

Upvotes: 5

Jason Lewis
Jason Lewis

Reputation: 18665

You can use DB::raw() to create a raw expression if you need to use things which would otherwise be escaped by Eloquent.

Refer to the documentation on raw expressions for some examples.

Upvotes: 1

Related Questions