Creating database view in migration laravel 5.2

Question

I'm trying to create a database view via a migration in Laravel 5.2 as I need to hand a fairly complex query to a view. I have models / tables for leagues, teams, players, and points. Each one has a hasMany / belongsTo relationship with the one before it. The goal is to create a table where each row is a league name, a sum of all remaining points for the league, and a count of points.remnants where the value of points.remnants > 4.

Major Edit:

What I have so far is

         DB::statement( 'CREATE VIEW wones AS
        SELECT
          leagues.name as name,
          sum(points.remnants) as trem,
          count(case when points.remnants < 4 then 1 end) as crem
     FROM leauges
       JOIN teams ON (teams.league_id = leagues.id)
       JOIN players ON (players.team_id = teams.id)
       JOIN points ON (points.player_id = players.id);
     ' );

This does not throw any errors, but it only returns one row and the sum is for all points in all leagues.

What I'm looking for is to create a table where there is a row for each league, that has league name, total remaining points for that league, and total points with less than 4 remaining per league.

Marked as solved. See the accepted answer for most of this issues. The one row problem was because I wasn't using GROUP BY with the count().

Answer 1

It looks to me like the problem is your SQL syntax. Here's what you wrote:

CREATE VIEW wones AS SELECT (name from leagues) AS name
join teams where (leagues.id = team.country_id)
join players where (teams.id = players.team_id)
join points where (players.id = points.player_id)
sum(points.remnants) AS trem
count(points.remnants where points.remnants < 4) AS crem

The problem is with the way you've mixed FROM and JOIN clauses with column specifications. Try this:

CREATE VIEW wones AS
SELECT
    leagues.name,
    sum(points.remnants) AS trem
    sum(IF(points.remnants<4, 1, 0)) AS crem
FROM leagues
    JOIN teams ON (leagues.id = team.country_id)
    JOIN players ON (teams.id = players.team_id)
    JOIN points ON (players.id = points.player_id);

I've reformatted it a bit to make it a little clearer. The SQL keywords are capitalized and the various clauses are separated onto their own lines. What we're doing here is specifying the columns, followed by the table specifications - first the leagues table, then the other tables joined to that one.