当表中不存在列时如何将 paginate() 与 having() 子句一起使用

问题描述

我有一个棘手的案例......

I have a tricky case ...

以下数据库查询不起作用:

Following database query does not work:

DB::table('posts')
->select('posts.*', DB::raw($haversineSQL . ' as distance'))
->having('distance', '<=', $distance)
->paginate(10);

失败并显示消息:列距离不存在.

It fails with message: column distance does not exist.

当 paginate() 尝试计算记录时发生错误

The error occurs when paginate() tries to count the records with

select count(*) as aggregate from {query without the column names}

由于列名被剥离，距离未知并引发异常.

As the column names are stripped, distance is not known and an exception is raised.

在这种情况下，有人可以使用分页吗?

Does somebody have a work around to be able to use pagination is this case ?

谢谢

推荐答案

可以在WHERE部分计算距离:

DB::table('posts')
    ->whereRaw($haversineSQL . '<= ?', [$distance])
    ->paginate(10);

如果您的应用程序中需要 distance 值，则必须计算两次:

If you need the distance value in your application, you'll have to calculate it twice:

DB::table('posts')
    ->select('posts.*', DB::raw($haversineSQL . ' as distance'))
    ->whereRaw($haversineSQL . '<= ?', [$distance])
    ->paginate(10);

这篇关于当表中不存在列时如何将 paginate() 与 having() 子句一起使用的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！