Welcome Guest, Not a member yet? Register   Sign In
Datamapper and pagination
#1

[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.
#2

[eluser]WanWizard[/eluser]
1.8.1. is a fairly old version. I can't check bitbucket atm (it's down) to see if something was fixed in later versions, but looking at the current code, I don't see any reason why any clause would get dropped from the query.

Perhaps you can try the 1.8.3-dev from bitbucket (when it's back online) to see if it indeed is fixed?

I assume it is, because in this version the count query is run on a cloned object, from which the select and order_by clauses are removed. It will definitely not strip any other clauses.
#3

[eluser]avramovic[/eluser]
Hm, if I just drop 1.8.3-dev in my project /app/libraries folder (along with /app/third_party/datamapper and /app/datamapper) I get following error:

Quote:Fatal error: Call to undefined method CI_DB_mysql_driver::dm_call_method() in D:\wwwroot\application\libraries\datamapper.php on line 1044

I've tried 1.8.2 also but I get the same error on line #1113

Any ideas?
#4

[eluser]WanWizard[/eluser]
Looks like you haven't added the bootloader to your index.php? Or you are autoloading the database class?
#5

[eluser]avramovic[/eluser]
It was working all fine with 1.8.1-dev

I'm autoloading both database and datamapper classes through application/config/autoload.php

Code:
$autoload['libraries'] = array('database', 'datamapper' /*, ... and so on*/ );

Has anything changed in DM loading procedure since 1.8.1?
#6

[eluser]WanWizard[/eluser]
Yes, quite a lot.

Due to the decision to make a lot of methods and properties in the database classes protected, DM could no longer access them. Because of this, DM now comes with a bootloader that replaces the CI classes, and adds methods to be able to access the internals. See http://datamapper.wanwizard.eu/pages/installation.html, step 9.

Without this bootloader, DM will no longer work on CI 2.1+.
#7

[eluser]avramovic[/eluser]
Ok, I've upgraded both DM and CI (DM 1.8.3-dev + CI 2.1.3) but the results are completely the same as in 1st post of this thread. I'm losing my mind, I'm now beginning to think the problem is somewhere in my code, but I never use ->limit() in this function, only ->get_paged($page, $per_page) but it keeps executing wrong query and tells me there is only one page :/
#8

[eluser]WanWizard[/eluser]
The problem might be here that you're using a count() query in combination with a group_by.

get_paged() runs two queries, one to determine the number of rows in the result, and one to actually get the rows for the required page.

For the first query, all select() and order_by() clauses are stripped, since they are not relevant for a count query. Unless you already have a query with a count() constructed, in which case this goes horribly wrong (as your grouping will no longer work).

Can you enable profiling, and post the SQL of the count query generated for this get_paged() call?
#9

[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)?
#10

[eluser]jairoh_[/eluser]
we cannot help you as much as you can w/ yourself because we don't see the structure or the values in your database. and you stated that "but there’s no time to change db structure now, so I need to come up with a solution". We'll if your database is not fully structured, believe me you'll get frustrated. maybe not yet now but for the future upgrades. database structure is the most critical part. Big Grin




Theme © iAndrew 2016 - Forum software by © MyBB