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().

Methoxychlor

DB::statement() will execute your query as it is, so my guess would be that the problem has nothing to do with laravel. Have you tried executing this query directly on the cli? – Leduc

Ah. I may be overthinking it then. If I were to execute it in sql the count and sum would be in the select before the joins. When I try that in laravel it throws an error – Methoxychlor

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.

Loganiaceous

You're mostly right. I know that I have let this attempt to translate everything into Eloquent get in my head and I am now making stupid mistakes with basic SQL syntax. The query as you arranged it looks much better but it still throws a syntax error at the count(). – Methoxychlor

I don't think that's right either as it would apply the WHERE clause to both the count() and the sum(), and I only want it applied to the count(). – Methoxychlor

Ok, one more edit - I'm assuming that you want the sum of points.remnants for all lines in the table, plus the count of the number of lines where points.remnants<4. The sum is adding 1 if the value is less than 4, or zero otherwise – Loganiaceous

I actually solved the count() issue, but I'm only returning results for one league in the view table rather than all of the leagues. I'll edit my question. – Methoxychlor