Datamapper and pagination

[eluser]avramovic[/eluser]
I have explained the DB structure in my first post. I have bunch of other fields in the table, but for this case I'm using only "location" as I'm trying to list all locations, display number of friends "assigned" to each location, and I need to sort locations by that number. And on top of that, I'm trying to paginate all these locations.

The project is very big and the employer is constantly adding "to-do" tasks. Listing locations in this manner was not planned when I started the project and I never intended to store locations in separate table (which would be of use now ). If I would change DB structure now, I'd have to rewrite half of my project, as there are other fields similar to "location" that need to be listed and paged :S I'm practically at the end, just need to solve this and send the employer to hell

So, has anyone tried to make "manual" pagination as I explained (or at least tried to) in my previous reply?

[eluser]kakallatt[/eluser]
How I can write a join query like that:

Code:

$this->db->select('pages.*, p.slug as parent_slug, p.title as parent_title');
  $this->db->join('pages as p', 'pages.parent_id=p.id', 'left');

Thank you very much.

[eluser]WanWizard[/eluser]
Assuming you have defined the relation correctly (which in this case would be a self-relation), just run an include_related?

[eluser]kakallatt[/eluser]
[quote author="WanWizard" date="1366837084"]Assuming you have defined the relation correctly (which in this case would be a self-relation), just run an include_related?[/quote]

I try:

Code:

$category->include_related('category', 'name');

But I get a message
Unable to relate category with category.

[eluser]WanWizard[/eluser]
Then your relationship definition is wrong, can you post it?

[eluser]kakallatt[/eluser]
[quote author="WanWizard" date="1366871686"]Then your relationship definition is wrong, can you post it?[/quote]

I dont' know how to defined relationship for self-relation.
My problem is: I have a parent_id and I want to use it for get the name of page

I usually use:

Code:

$this->db->select('pages.*, p.slug as parent_slug, p.title as parent_title');
  $this->db->join('pages as p', 'pages.parent_id=p.id', 'left');

But in datamapper ORM, I don't know how to defined the relation correctly. Thank you