Creating a database view during laravel 5.2 migration

Question

I am trying to create a database view using migration in Laravel 5.2, since I need to pass a rather complex query to the view. I have models / tables for leagues, teams, players and points. Each of them has hasMany / belongs to what came before it. The goal is to create a table in which each row is the name of the league, the sum of all the remaining points for the league and the number of points., Where the value of points.remnants> 4.

The main change:

What am i still

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 cause any errors, but returns only one row, and the sum is for all points in all leagues.

I am looking to create a table in which there is a row for each league, in which there is a league name, the total remaining points for this league and the total points with less than 4 remaining in the league.

Marked as resolved. See the accepted answer for most of these questions. The problem with one line was that I did not use GROUP BY with count ().

Answer 1

It seems to me that the problem is the syntax of SQL. Here is 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 how you mixed the FROM and JOIN clauses with the column specifications. Try the following:

 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 reformatted it a bit to make it a little clearer. SQL keywords are capitalized, and various sentences are split into their own lines. Here we indicate the columns, and then the table specifications — first the leagues table, then the other tables associated with it.