英文:
How to correctly use whereNotNull() in Laravel's collections? (Not working as I'd thought)
问题
我正在编写这个应用程序来进行NFL比赛的办公室游戏，所以我有一个表格，用来存储比赛结果，基本上包括比赛的日期和时间，客队和主队，以及比分。现在我想编写一个模型属性来获取球队的战绩（胜-负-平），所以首先我要知道球队打了多少场比赛，我正在尝试以下代码：
$games = Game::whereNotNull('homescore')
->whereNotNull('awayscore')
->where('home', 'PIT')
->orWhere('away', 'PIT')
->get()
所以这应该给我所有由PIT作为客队或主队参与的比赛，而且只有比分已经存在的比赛，对吗？
嗯... 这不起作用。如果我去掉->whereNotNull()，我可以正确地获得PIT在赛季中将参加的17场比赛。但如果我使用它们，它给我10场比赛。而且不是随机的，它总是给我相同的10场比赛。在所有这些比赛中，PIT至少参与其中。
这是我得到的记录之一：
App\Models\Juego {
id: 4010,
season: 2022,
week: 4,
valid: "2022-10-02 12:00:00",
away: "NYJ",
awayscore: null,
home: "PIT",
homescore: null,
dif: null,
created_at: "2023-02-05 22:24:09",
updated_at: "2023-02-06 13:18:26",
}
我甚至尝试将查询分成两部分，像这样：
$games = Game::where('home', 'PIT')->orWhere('away', 'PIT')
（这给出了17个正确的结果），然后在这个结果上应用$games->whereNotNull('homescore')和awayscore，仍然给我10个结果（实际查询应该给我4个结果，其中有比赛的最终比分）。
英文:
I'm writing this app to do some office pool on the NFL games, so I have a table where I store the game results, it basically has date and time of the game, visitor and local, and scores. Now I want to write a model attribute to get the team's record (W-L-D), so first I have to get how many games the team has played, I'm trying this:
$games = Game::whereNotNull('homescore')
->whereNotNull('awayscore')
->where('home','PIT')
->orWhere('away','PIT')
->get()
So this should give me all the games played by PIT as away or home, and only the games where a score is present, right?
Well... it's not working. If I take away both ->wherNotNUll() I correctly get the 17 games PIT will play in a season. But if I use them it gets me 10 games. Not at random, it alway get me the same 10 games. In all those games PIT is involved, at least.
This is one of the records I'm getting:
App\Models\Juego {#4788
id: 4010,
season: 2022,
week: 4,
valid: "2022-10-02 12:00:00",
away: "NYJ",
awayscore: null,
home: "PIT",
homescore: null,
dif: null,
created_at: "2023-02-05 22:24:09",
updated_at: "2023-02-06 13:18:26",
},
I even break up the query doing something like:
$games = Game::where('home','PIT')->orWhere('away','PIT')
(This gives 17 correct results) and then over that result applying the $games->whereNotNull('homescore') and awayscore and still gives me the 10 results (the query should be giving me 4 results, where there are final score for the game)
答案1
得分: 4
因为结合了 whereNotNull、where 和 orWhere，导致了一团糟。
orWhere 会破坏你整个 SQL 查询逻辑，并不会按你的期望方式工作。
只需要将 where/orWhere 放入一个闭包中，就应该没问题。
Game::whereNotNull('homescore')
->whereNotNull('awayscore')
->where(function ($query) {
$query->where('home', 'PIT')
->orWhere('away', 'PIT');
})
->get();
简单解释一下，这是你查询的原始 SQL，一切都很好，直到添加 OR 为止：
WHERE homescore IS not NULL
AND awayscore IS not NULL
AND home = 'PIT'
OR away = 'PIT'
使用闭包后，它会变成这样（这可能是你想要的）：
WHERE homescore IS not NULL
AND awayscore IS not NULL
AND (home = 'PIT' or away = 'PIT')
英文:
its become a mess because of the combination of whereNotNull, where and orWhere
the orWhere messes up your whole sql query logic and does not work the way you expect.
Just put the where/orWhere in a closure and it should be fine
Game::whereNotNull('homescore')
->whereNotNull('awayscore')
->where(function ($query) {
$query->where('home', 'PIT')
->orWhere('away', 'PIT');
})
->get();
just a bit of explanation,
this is the raw sql of your query, everything is fine until the OR is added
WHERE homescore IS not NULL
AND awayscore IS not NULL
AND home = 'PIT'
OR away = 'PIT'
with closure it becomes like this (which is probably what you are wanting)
WHERE homescore IS not NULL
AND awayscore IS not NULL
AND (home = 'PIT' or away = 'PIT')
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论