CODEWITHSUNDEEP
phplaravel-5eloquent
Yargo.ar
Yargo.ar

Reputation: 83

How to translate a SQL to Eloquent?

How can I transform this current sql statement into Eloquent and have results grouped by team_id inside a grouped by group_id?

SELECT 
    max(`group_id`) as group_id,
    `team_id` as team_id, 
    max(level_id) as level_id, 
    sum(`played`) as played,
    sum(`won`) as won,
    sum(`draw`) as draw,
    sum(`lost`) as lost,
    sum(`gf`) as gf,
    sum(`ga`) as ga,
    sum(`gd`) as gd,
    sum(`points`) as points 
FROM `group_team` 
WHERE level_id = 1 
GROUP BY team_id

Upvotes: 0

Views: 95

Answers (2)

HSLM
HSLM

Reputation: 2012

my best try is:

knowing that GroupTeam is your Model 

GroupTeam::where('level_id', 1)
    ->groupBy('team_id')
    ->addSelect(
        DB::raw('*, 
            max(`group_id`) as group_id,
            `team_id` as team_id, 
            max(level_id) as level_id, 
            sum(`played`) as played,
            sum(`won`) as won,
            sum(`draw`) as draw,
            sum(`lost`) as lost,
            sum(`gf`) as gf,
            sum(`ga`) as ga,
            sum(`gd`) as gd,
            sum(`points`) as points'
        )   
    )->get();

</code>

Upvotes: 1

Vinicius Cainelli
Vinicius Cainelli

Reputation: 974

Something like this

$query= DB::table('group_team')
    ->max('group_id')
    ->where('level_id',1)
    ->groupBy('team_id')
    ->get();

Or you can run raw sql to query direct to db, like this

$query = DB::select('SELECT max("group_id") as group_id' FROM group_team WHERE level_id = 1 GROUP BY team_id);

For a better explanation, check the documentation: https://laravel.com/docs/5.5/queries

Upvotes: 1

Related Questions