Model with calculated sql field doesnt paginate

replied 9 years ago

Solution

It seems the bindings are screwing up the aggregate query which is used when paginating.

so solution is something like this... Illuminate\Database\Query\Builder.php

// line 1557
public function getPaginationCount()
{
$this->backupFieldsForCount();
$columns = $this->columns;

// Because some database engines may throw errors if we leave the ordering
// statements on the query, we will "back them up" and remove them from
// the query. Once we have the count we will put them back onto this.

$this->_bindings = $this->bindings; // back up the bindings
$this->bindings = array(); // reset the bindings to empty array

$total = $this->count();
$this->restoreFieldsForCount();


$this->bindings = $this->_bindings; // restore the bindings
// Once the query is run we need to put the old select columns back on the
// instance so that the select query will run properly. Otherwise, they
// will be cleared, then the query will fire with all of the columns.
$this->columns = $columns;

return $total;
}

Last updated 1 year ago.

0

kpzani

replied 9 years ago

Well my solution doesnt actually fix it properly cutting off the bindings breaks the WHERE criteria

so i had to rewrite my query to

$q = trim(Input::get('q', ''));
		$q_plus = "+" . str_replace(" ", " +", $q);
		$q_escaped = DB::connection('mysql-search')->getPdo()->quote( $q );

		$matchcol = <<<TEXTBLOCK
(
	(match (`search`.`slug`) against ({$q_escaped} IN NATURAL LANGUAGE MODE) * 4) 
+	(match (`search`.`full_title`) against ({$q_escaped} IN NATURAL LANGUAGE MODE) * 3) 
+	(match (`search`.`sectors`) against ({$q_escaped} IN NATURAL LANGUAGE MODE) * 2) 
+	(match (`search`.`content`) against ({$q_escaped} IN NATURAL LANGUAGE MODE))
) 
TEXTBLOCK;
		
		$match = "match (`search`.`slug`, `search`.`full_title`, `search`.`sectors`, `search`.`content`) against (? IN BOOLEAN MODE)";

		$results = Search::select(
			DB::Raw('version_id, slug, full_title, ' . $matchcol . ' as score') )
			->whereRaw($match, array($q_plus))
			->orderby('score', 'DESC')
			->paginate(12);

not the best but it works :-)

Last updated 1 year ago.

0

abelgo

replied 9 years ago

  $results = Search::select(
  	DB::Raw('version_id, slug, full_title, ' . $matchcol . ' as score') )
  	->whereRaw($match, array($q_plus))
  	->orderby('score', 'DESC')
  	->paginate(12);


not the best but it works :-)>**kpzani** said:

:-) Hmmm ! I think I know how you feel ...

Recently I've also been having quite a few of these type of problems. Here is my I don't have to time to deal with this solution..

Go to database application type

CREATE VIEW data_like_i_want_it AS
SELECT col1 as wonderful_name,    col2 * col2  as 'price' ,  unix_timestamp()  as 'timestamp'
FROM crap_model JOIN other_stuff
WHERE other_stuff.not_included = true

The one line model


Class  NoNeedToScrewAround extends Eloquent  {

    protected $table = 'data_like_i_want_it';

 }


$results = NoNeedToScrewAround::all()->get( array( ' wonderful_name' , 'price' , 'timestamp'   )   );

My time is more valuable and I need a reliable solution that is not going to break.

Last updated 1 year ago.

0

Moderators