Codeigniter+MySql: Integrate COUNT into this query to avoid double queries when paginating

Question

Since pagination in Codeigniter (IMHO) lack some good stuff for easier implementation when using filters (beginning letter, category, etc.), even though my pagination is fully functional and working - i have two queries running and the only difference between them is that one of them has LIMIT (and OFFSET) at the end. This second query is needed because i have to get the total number of rows and with my "main" query i can't get it because it has LIMIT so it returns limited number of rows.

QUERY 1 ("main" query)

SELECT art.*, songs.numsongs 
        FROM _tbl_artists AS art
        LEFT JOIN 
        (SELECT _ARTIST_ID, COUNT(*) AS numsongs 
        FROM _tbl_songs GROUP BY _ARTIST_ID) AS songs 
        ON art._ID_ORIGINAL = songs._ARTIST_ID 
        WHERE art._artist_type = 0 AND art._ARTIST_NAME LIKE 'C%' AND art.id > 0 ORDER BY art._ARTIST_NAME ASC LIMIT 20, 20

QUERY 2 (this one counts rows)

SELECT art.*, songs.numsongs 
        FROM _tbl_artists AS art
        LEFT JOIN 
        (SELECT _ARTIST_ID, COUNT(*) AS numsongs 
        FROM _tbl_songs GROUP BY _ARTIST_ID) AS songs 
        ON art._ID_ORIGINAL = songs._ARTIST_ID 
        WHERE art._artist_type = 0 AND art._ARTIST_NAME LIKE 'C%' AND art.id > 0 ORDER BY art._ARTIST_NAME ASC

Model code (it's messy, i know :) ):

function loadArtists($type='', $letter='', $uriOffset=0, $perPage='')
{
    $letterEval = preg_match('[0-9]', $letter);
    switch($letterEval):
        case 1:
            $operator = 'REGEXP';
            $letter = "^[0-9]";
            $s = '';
            break;
        case 0:
            $operator = 'LIKE';
            $letter = "$letter";
            $s = '%';
            break;
        default: 0; break;
    endswitch;

    $query = "SELECT SQL_CALC_FOUND_ROWS art.*, songs.numsongs 
        FROM _tbl_artists AS art
        LEFT JOIN 
        (SELECT _ARTIST_ID, COUNT(*) AS numsongs 
        FROM _tbl_songs GROUP BY _ARTIST_ID) AS songs 
        ON art._ID_ORIGINAL = songs._ARTIST_ID 
        WHERE";
        if($type == 0 || $type == 1)
            $query .= " art._artist_type = $type AND";

        if($letter != '')
            $query .= " art._ARTIST_NAME $operator '$letter$s' AND";
        else
            $query .= '';

    $query .= " art.id > 0 ORDER BY art._ARTIST_NAME ASC";

    if ($uriOffset != '')
        $limited = $query . " LIMIT $uriOffset, $perPage";
    else
        $limited = $query . " LIMIT $perPage";

    $noLimit = $this->db->query($query);
    $withLimit = $this->db->query($limited);

    $numRows = $withLimit->num_rows(); 

    $resultStack = array();
    $resultStack = array($numRows);


    if($withLimit->num_rows() > 0)
    {
        $result = $withLimit->result();
        $message = '';
    }

    else
    {
        $result = NULL;
        $message = 'Nema rezultata';
    }

    array_push($resultStack, $result, $message);

    return $resultStack; //$resultStack;
}

So what i actually need is, instead of having these 2 complicated queries running, i'd rather make one query even if it get more complicated but i want to get total rows count from it.

I read something about sql_calc_found_rows but couldnt implement it because i'm totally unfamiliar with it and also many say that it puts additional strain to the database so it should be used only in rare cases.

Any help will be appreciated!

What, exactly, is it you're trying to do with that second statement (that is, `$noLimit`)? Are you trying to get all of the rows, a summation of all artists or songs, or what? Depending on what you're tring to do, it'd be better to leave it as two queries just for *maintainability*, regardless of any possible performance issues. — Clockwork-Muse, Jul 14 '11 at 20:47
Note that $noLimit contains number of records of the query when it doesnt have that LIMIT at the end. Imagine this: you want to display all the artists or songs, and they may and may not be narrowed by such things as genre, first letter, etc.. Now, that's cool, it's easy to create such a query but my pagination script wants from me to have a defined number of records per page, all at the same time. Thus, i decided to count the rows in $noLimit and use it as the $total_records for pagination, and $withLimit records are also sent to the pagination script as an item which defines $per_page config — developer10, Aug 22 '11 at 22:01
If you have any suggestions for me to make this simpler, I'd like to hear it! — developer10, Aug 22 '11 at 22:07

score 2 · Accepted Answer · answered Aug 22 '12 at 00:24

You could do something like this to obtain the total number of (unpaginated) rows:

(See the output on SQL Fiddle)

SELECT art.*, songs.numsongs, artist_count.total
    FROM _tbl_artists AS art
    JOIN (
        SELECT COUNT(*) as total
        FROM _tbl_artists as art
        WHERE art._artist_type = 0
        AND art._ARTIST_NAME LIKE 'C%' AND art.id > 0
    ) artist_count
    LEFT JOIN (
        SELECT _ARTIST_ID, COUNT(*) AS numsongs 
        FROM _tbl_songs GROUP BY _ARTIST_ID
    ) AS songs ON art._ID_ORIGINAL = songs._ARTIST_ID 
    WHERE art._artist_type = 0
    AND art._ARTIST_NAME LIKE 'C%' AND art.id > 0
    ORDER BY art._ARTIST_NAME ASC
    LIMIT 20, 20;

The first subquery just uses the conditions you've provided for searching for artists and counts the total rows (artists) that meet those conditions. We don't need to JOIN to the _tbl_songs table here as we don't care whether these artists have any songs in this subquery.

Codeigniter+MySql: Integrate COUNT into this query to avoid double queries when paginating

1 Answers1