Double Query? Is there a better way?

[eluser]helmutbjorg[/eluser]
Hi Guys,

I'm using ignitedrecord but this problem is the same in active record.

Say i have the following

Code:

$this->user->select('name');
$this->user->where('state', 'something');
$this->user->join('postcodes', 'postcodes.code = users.postcode');
$this->user->find_all();

Now that is a rather simple query. Let's say I want to use the pagination class I am required to do this...

Code:

// Get total count for pagination
$this->user->select('name');
$this->user->where('state', 'something');
$this->user->join('postcodes', 'postcodes.code = users.postcode');
$config['total_rows'] = $this->user->count();
$this->pagination->initialize($config); // Theres more here but you get the gist

// Now do the query again to get the actual rows
$this->user->select('name');
$this->user->where('state', 'something');
$this->user->join('postcodes', 'postcodes.code = users.postcode');
// With the limit in place
$this->user->limit(?);
$this->user->offset(?);
$this->user->find_all();

Notice how I needed to write the main parts of the query twice. Some of my querys are reasonably lengthy and it is kind of annoying have to repeat the same code simply to get the count and then do it all again to just add the offset and limit. Is there a better way?

[eluser]richthegeek[/eluser]
you can add a selector to your second set of statements such that your query now looks something like this:

Code:

SELECT name, (SELECT COUNT(*) FROM ...) as total_rows FROM... LIMIT ...

That will work on a MySQL 5.1+ system, sorry that I'm not sure how to do it with ignitedrecord/activerecord

[eluser]xwero[/eluser]
I guess IR is using the CI AR class so you could try

Code:

$this->user->start_cache();
$this->user->select('name');
$this->user->where('state', 'something');
$this->user->join('postcodes', 'postcodes.code = users.postcode');
$this->user->stop_cache();
// Get total count for pagination
$config['total_rows'] = $this->user->count();
$this->pagination->initialize($config); // Theres more here but you get the gist

// With the limit in place
$this->user->limit(?);
$this->user->offset(?);
$this->user->find_all();
$this->user->flush_cache();

[eluser]m4rw3r[/eluser]
Xwero is totally correct (but I would select more than just name, eg. PK too) except that IR does not use CI's AR, it uses my own SQL-building lib.

[eluser]kgill[/eluser]
Since you're talking about limit I'm working on the assumption you're using MySQL - look in to SQL_CALC_FOUND_ROWS and the FOUND_ROWS() function. Basically you write your select with limit statement but include the SQL_CALC_FOUND_ROWS keyword just after "select" and then execute a "select found_rows()" after the 1st query to get the total rows; still two queries but no retyping things.

[eluser]helmutbjorg[/eluser]
Thanks for the great responses. I was hoping there would be some kind of query cache or save function so thanks. One other possible way which I tried is to wrap the query building inside a function. And then call that function twice. kgill - i like the portability of not using database type specific vars and functions but that looks like a sweet alternative. It isn't wrapped up in the Active Record library is it?