php Laravel Eloquent，其中字段为 X 或 null

Laravel Eloquent where field is X or null

提问by festie

I have a table like this:

我有一张这样的表：

table
- field1: tinyint
- field2: varchar (nullable)
- datefield: timestamp (nullable)

Now I want to get all entries where field1 is 1, field2 is null and where datefield is smaller than X or null. I already tried something like this:

现在我想获取 field1 为 1、field2 为 null 且 datefield 小于 X 或 null 的所有条目。我已经尝试过这样的事情：

$query = Model::where('field1', 1)
            ->whereNull('field2')
            ->where('datefield', '<', $date)
            ->orWhereNull('datefield');

but thats not working. I always get every entry where datefield is null. It doesn't matter what the other fields are. I also tried to split it in 2 queries: First get every row where datefield is smaller than X or null and then (based on it) get every field where field1 is 1 and field2 is null.

但这不起作用。我总是得到日期字段为空的每个条目。其他字段是什么并不重要。我还尝试将其拆分为 2 个查询：首先获取 datefield 小于 X 或 null 的每一行，然后（基于它）获取 field1 为 1 且 field2 为 null 的每个字段。

The result was the same. Any idea how to do this?

结果是一样的。知道如何做到这一点吗？

回答by James

It sounds like you need to make use of advanced where clauses.

听起来您需要使用高级 where 子句。

Given that search in field1and field2is constant we will leave them as is, but we are going to adjust your search in datefielda little.

鉴于搜索field1和field2不变，我们将保持原样，但我们将datefield稍微调整您的搜索。

Try this:

尝试这个：

$query = Model::where('field1', 1)
    ->whereNull('field2')
    ->where(function ($query) {
        $query->where('datefield', '<', $date)
            ->orWhereNull('datefield');
    }
);

If you ever need to debug a query and see why it isn't working, it can help to see what SQL it is actually executing. You can chain ->toSql()to the end of your eloquent query to generate the SQL.

如果您需要调试查询并查看它为什么不起作用，那么查看它实际执行的 SQL 会有所帮助。您可以链接->toSql()到 eloquent 查询的末尾以生成 SQL。

回答by kaleazy

You could merge two queries together:

您可以将两个查询合并在一起：

$merged = $query_one->merge($query_two);