字段为 X 或空的雄辩

我有一张这样的桌子:

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

现在我想得到所有 field1为1，field2为 null，datefield 小于 X 或 null 的条目。我已经试过这样的方法了:

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

但这不管用。我总是得到 datefield 为 null 的每个条目。其他领域是什么并不重要。我还尝试将它分成两个查询: 首先获取 datefield 小于 X 或 null 的每一行，然后(根据它)获取 field1为1、 field2为 null 的每一个字段。

结果是一样的。 知道怎么做吗？

258713 次浏览
小开
最佳答案

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

Given that search in field1 and field2 is constant we will leave them as is, but we are going to adjust your search in datefield a little.

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.

小开

You could merge two queries together:

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

Using coalesce() converts null to 0:

$query = Model::where('field1', 1)
->whereNull('field2')
->where(DB::raw('COALESCE(datefield_at,0)'), '<', $date)
;
小开

If you are confused about where to put the get()/first() for getting the collection or a single row here is the way:

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

提交答案