在迁移 laravel 5.2 中创建数据库视图
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/35140069/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Creating database view in migration laravel 5.2
提问by Blind Fish
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.
我正在尝试通过 Laravel 5.2 中的迁移创建一个数据库视图，因为我需要将一个相当复杂的查询交给一个视图。我有联赛、球队、球员和积分的模型/表格。每个都有一个 hasMany/belongsTo 与它之前的关系。目标是创建一个表，其中每一行是一个联赛名称、该联赛所有剩余积分的总和以及 points.remnants 的计数，其中 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.
我正在寻找的是创建一个表格，其中每个联赛都有一行，其中包含联赛名称、该联赛的总剩余积分以及每个联赛剩余积分少于 4 的总积分。
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().
标记为已解决。请参阅大多数此类问题的公认答案。一行问题是因为我没有将 GROUP BY 与 count() 一起使用。
采纳答案by Kryten
It looks to me like the problem is your SQL syntax. Here's what you wrote:
在我看来，问题在于您的 SQL 语法。这是你写的：
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 FROMand JOINclauses with column specifications. Try this:
问题是与你混合的方式FROM和JOIN使用的条款列规范。尝试这个：
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 leaguestable, then the other tables joined to that one.
我重新格式化了一下，使它更清晰一点。SQL 关键字是大写的，并且各个子句分开到各自的行中。我们在这里所做的是指定列，然后是表规范——首先是leagues表，然后是连接到该表的其他表。
