当表中不存在列时如何使用带有hading()子句的paginate() [英] How to use paginate() with a having() clause when column does not exist in table

问题描述

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

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部分中计算距离:

You can calculate the distance in the WHERE part:

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);

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