[eluser]avramovic[/eluser]
I have a problem with pagination of specific field selection. Using Datamapper 1.8.1-dev and CI 2.0.2.
I have table "friends" set up like this (only important fields displayed):
Code:
id | name | location
Now, many "friends" share same location so locations could go into different table, but there's no time to change db structure now, so I need to come up with a solution to list all locations and corresponding number of friends belonging to that location. Here's my code:
Code:
$locations = new Friend();
$locations->select('location, count(*) as friend_count')
->group_by('location')
->order_by('location ASC')
->get_paged(2, 50); //note the 2nd page, set for testing
This works fine, except it returns 1st page, and only 1st page. Here's the SQL:
Code:
SELECT `location`, count(*) AS friend_count
FROM
(`friends`)
GROUP BY `friends`.`location`
ORDER BY
`friends`.`location` ASC
LIMIT 50
The output of
$locations->paged is this:
Code:
object(stdClass)[274]
public 'page_size' => int 50
public 'items_on_page' => int 50
public 'current_page' => float 1
public 'current_row' => float 0
public 'total_rows' => int 1
public 'last_row' => float 0
public 'total_pages' => float 1
public 'has_previous' => boolean false
public 'previous_page' => int 1
public 'previous_row' => int 0
public 'has_next' => boolean false
public 'next_page' => float 1
public 'next_row' => float 0
As soon as I remove the "group_by" (and friends_count; as without "group_by" it would return only one row), it works fairly fine:
Code:
$locations = new Friend();
$locations->select('location')
->order_by('location ASC')
->get_paged(2, 50); //again 2nd page for testing
SQL:
Code:
SELECT `location`
FROM
(`friends`)
ORDER BY
`friends`.`location` ASC
LIMIT 50, 50
The output of
$locations->paged:
Code:
object(stdClass)[50]
public 'page_size' => int 50
public 'items_on_page' => int 50
public 'current_page' => int 2
public 'current_row' => int 50
public 'total_rows' => int 793
public 'last_row' => float 750
public 'total_pages' => float 16
public 'has_previous' => boolean true
public 'previous_page' => int 1
public 'previous_row' => int 0
public 'has_next' => boolean true
public 'next_page' => int 3
public 'next_row' => int 100
The problem with this second method is that I must additionally count number of friends for each location, which is slowing the page a bit, but that's not important, the important thing is I can't sort locations by number of "friends" and I MUST have ability to sort it by "friends_count".
The sub-question of this issue is: When I use
get_paged() on such field selection, it counts wrong number of pages as it seems the second query (used to get total number of rows) simply executes SELECT count(*) FROM friends, returning the total number of rows in the table and totally ignoring my selection... any idea how to overcome this (excluding total re-construction of the database)? In above example I have total of 793 friends, but number of locations is significantly less than 793.
Note that my project is much more complex than this so I have tried to simplify things here as much as I could. Some of the above codes may contain errors.
