CODEWITHSUNDEEP
phpmysqlsqllaravellaravel-4
Ara Sivaneswaran
Ara Sivaneswaran

Reputation: 375

Complicated query using eloquent - Laravel 4

So I am making a web site for an simulated hockey league.

I am trying to make the standings and this is where it gets complicated.

There is 2 conferences. East and West.

In each conference, there is 3 divisions, for the sake of the example, let's call them A,B and C.

There is 5 teams per division, Team 1, Team 2, Team 3, Team 4, Team 5.

So here is where it get's tricky.

The standings are made up by conference. So for example, there is a separate standings for East and West. The top team of each division are automatically seeded in the top 3 spots in the conference.

For example, Team 1 in Division A has 5 points, Team 2 in Division A has 4, Team 4 in Division B has 4 points (the rest got less), Team 5 in Division C has 3 points (the rest got less). The standings should be: 1- Team 1 - Division A - 5 points 2- Team 4 - Division B - 4 points 3- Team 5 - Division C - 3 points 4- Team 2 - Division A - 4 points ...

The code I have is:

$teams_east = DB::query("SELECT a.*, (CASE WHEN b.scoreMax IS NULL THEN 1 ELSE 2 END) AS SortFiddle
                                FROM (SELECT teams.*, teamdetails.division, ((`nhl_wins` *2) + `nhl_ot` + `nhl_ties`) AS scoreMax
                                FROM teams
                                LEFT JOIN teamdetails ON teams.team_name = teamdetails.pro_name
                                WHERE teamdetails.conference ='Est'  ) a
                                LEFT OUTER JOIN
                                (SELECT division, MAX((`nhl_wins` *2) + `nhl_ot` + `nhl_ties`) AS scoreMax
                                FROM teams
                                LEFT JOIN teamdetails ON teams.team_name = teamdetails.pro_name
                                WHERE teamdetails.conference ='Est'  GROUP BY division ORDER BY `nhl_wins`,scoreMax LIMIT 3) b
                                ON a.division = b.division
                                AND a.scoreMax = b.scoreMax
                                ORDER BY SortFiddle DESC, a.scoreMax DESC, `nhl_wins` DESC, nhl_gf DESC, nhl_ga ASC");

But DB::query is deprecated and plus, I would like to use the eloquent because this same kind of code is going to be used somewhere else.

Upvotes: 2

Views: 1264

Answers (2)

ronscript
ronscript

Reputation: 407

I believe if you have Users model, you can use 

User::where('status', '<>', 1)
      ->groupBy('status')
      ->get([DB::raw('count(*) as user_count, status')]);

Upvotes: 0

Antonio Carlos Ribeiro
Antonio Carlos Ribeiro

Reputation: 87719

I don't believe CASE is available on QueryBuilder already. So you have two options:

This is a replacement for DB::query():

DB::select(DB::raw('select * from users'));

Or you can create all of it using QueryBuilder and the CASE part you can add some raw, like this:

$users = DB::table('users')
    ->select(DB::raw('count(*) as user_count, status'))
    ->where('status', '<>', 1)
    ->groupBy('status')
    ->get();

Upvotes: 1

Related Questions