Model with calculated sql field doesnt paginate

replied 10 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 2 years ago.

0

kpzani

replied 10 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 2 years ago.

0

abelgo

replied 10 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 2 years ago.

0

Moderators