0

I'm doing a search query by distance and search term, but when using "Having" in eloquent, the standard pagination no longer works.

Here is my code:

    $haversine = '( 3959 * acos( cos( radians(?) ) * cos( radians( latitude ) ) * cos( radians( longitude ) - radians(?) ) + sin( radians(?) ) * sin( radians( latitude ) ) ) )';
    $stores = Store::select(
        DB::raw("*, $haversine AS distance"))
        ->having("distance", "<", $input['distance'])
        ->orderBy("distance")
        ->setBindings(array(
            $latitude,
            $longitude,
            $latitude
        ))
        ->with(array(
            'offers' => function($query) {
                if(Input::has('term')) {
                    $query->whereHas('product', function ($product_query) {
                        $product_query->where(function($term_query) {
                            $fields = array('name', 'description','model_number');
                            foreach($fields as $field) {
                                $term_query->orWhere($field, 'LIKE', '%' . Input::get('term') . '%');
                            }
                            $term_query->orWhereHas('brand', function($brand_query) {
                                $brand_query->where('name', 'LIKE', '%' . Input::get('term') . '%');
                            });
                        });
                    });
                }
                $query->orderBy('price', 'ASC');
            })
        )
        ->get();

This query works perfectly without the pagination, but when trying the standard ->paginat(10) I get the following:

SQLSTATE[42S22]: Column not found: 1054 Unknown column 'distance' in 'having clause' (SQL: select count(*) as aggregate from stores having distance < 26.1817)

I've done plenty of searching for this answer, but I'm not sure why the solutions I've found do not work for me.

I've already looked at: https://github.com/laravel/framework/issues/3105

and

How to use paginate() with a having() clause when column does not exist in table

Please give me some guidance if you have dealt with this issue in the past.

Edit:

Here is the code that I tried, but did not work properly for me. 

$current_page = Paginator::getCurrentPage();
    $paginated_query = clone $stores;
    $paginated_query->addSelect('stores.*');

    $items  = $paginated_query->forPage($current_page, 10)->get();
    $totalResult = $stores->addSelect(DB::raw('count(*) as count'))->get();
    $totalItems = $totalResult[0]->count;

    $stores = Paginator::make($items->all(), $totalItems, 10);
Community
  • 1
  • 1
Andy
  • 25
  • 5
  • You referenced some links helpful to the problem in your question. Where's the code you tried that implements the workarounds described there? It would help to see that, to determine if you might have missed something. – Bogdan Mar 07 '15 at 01:02
  • Hi Bogdan, I've updated the question with the attempted code – Andy Mar 10 '15 at 18:13

1 Answers1

0

I was having the same problem with pagination using 'having' clause in eloquent.

This worked for me:

Instead of using alias of a column in the 'having' clause, use the actual calculation.

Instead of: 

$model->select(DB::raw("*, $haversine AS distance"))->having("distance", "<", $input['distance']);

Try this:

$model->select("*")->having(DB:raw($haversine), "<", $input['distance']);
Tomi
  • 245
  • 1
  • 8