Datamapper and pagination

[eluser]avramovic[/eluser]
Here's the first query:

Code:

SELECT COUNT(*) AS `numrows`
FROM (`friends`)
WHERE `friends`.`location` != ''
GROUP BY `friends`.`location`

...and the second:

Code:

SELECT `location`, count(*) AS friend_count
FROM
(`friends`)
WHERE `friends`.`location` != ''
GROUP BY `friends`.`location`
ORDER BY
`friends`.`location` ASC
LIMIT 50

Actually I have removed `WHERE `friends`.`location` != ''` part from my code and queries in my 1st post as I thought it was irrelevant (some "friends" do not have their location set and when listing locations I don't want blank row(s)), but boy it is relevant! With this WHERE clause 1st query returns 238 rows (which I believe is the number of locations) and each of them contains numbers 1-137 (which I believe is number of related friends). Without this clause the query returns 239 rows, with first one having number ~23000 (which IS the number of friends) and I think pagination reads last returned row (which contains number 1) so it thinks there is only one page.

I would like to go back to the end of my 1st post. When selecting only (distinct) fields, this first query (that counts numrows) should not remove SELECT clause, as I want to get unique number of locations, not number of friends in this table. But I guess it would be much slower to get everything from the table, count returned rows, and then query the database again to get only one page?

And finally, is there a quick way to solve this? Can I just clone the $friends object before calling get_paged(), then call get() on cloned object, count rows, calculate ->paged object properties and then manually get selected page with ->limit() and ->get() (on $friends, not on cloned object)?