I need help with complex relationships and queries using DataMapper

[eluser]qureshi[/eluser]
Hi,

First off: Thanks so much for CodeIgniter. To me it is pure beauty in action. I'm a newbie PHP programmer, but with CodeIgniter I feel like I have PHP superpowers!

I'm building a Quran studytool, and I've run in to some problems. I would really appreciate some help.

In my database, a user can create categories. So we have these tables: users, categories and categories_users.

The categories in turn can have verses. The tables are: verses and categories_verses.

The verses themselves have many translations. So we get: translations and translations_verses.

As you see, starting from a category and getting to the translations requires using two relations: categories_verses and then translations_verses.

But how do I do this with CodeIgniter/DataMapper? I know how to use one relation, but don't get how to use two sequentially.

Thanks for reading.

Regards,

Nadeem Qureshi

[eluser]oddman[/eluser]
I don't have experience with data mapper, but due to the complexity of this request, I would be throwing it into a single query, back to good 'ol pure SQL =P

Code:

SELECT * FROM categories c
    LEFT JOIN categories_verses v ON v.category_id = c.id
        LEFT JOIN translations_verses t ON t.verse_id = v.id
WHERE c.id = ?
AND t.language_id = ?

Hope that makes sense. I'm making a few assumptions here:

1. That you're simply getting a list of verses for the active translation for that category
2. I'm assuming the various IDs and fields used

Be sure to limit what you're actually selecting for performance

[eluser]qureshi[/eluser]
Hi,

Thanks for replying! I'm not that good at SQL, but at least you've pointed me in a direction: Left Joins. I'll read more about them and see if I can use them.

I was kinda hoping, since I've re-done my whole DB to suit DataMapper rules, i wouldn't have to use such complex queries.

Thanks again,

Nadeem Qureshi

[eluser]bEz[/eluser]
See Reply at the DataMapper thread located in Ignited Code!

[eluser]bEz[/eluser]
@qureshi
Using the DMZ edition of DataMapper, I would have Tables/Modles setup as follows:
Did not include the Translator portion, therefore the $has_many is empty!

Code:

users :: id, ...

class User extends DataMapper {
  
   var $table = 'users';
   var $has_many = array('category');
   var $has_one = array();
  
    function User()
   {
      parent::DataMapper();
    }
    
}

Code:

categories :: id, user_id, ...
(scrap categories_users)

class Category extends DataMapper {
  
   var $table = 'categories';
   var $has_many = array('verse');
   var $has_one = array('user');
  
    function Category()
   {
      parent::DataMapper();
    }
    
}

Code:

verses :: id, category_id, ...
(scrap categories_verses)

class Verse extends DataMapper {
  
   var $table = 'verses';
   var $has_many = array('translation');
   var $has_one = array('category');
  
    function Verse()
   {
      parent::DataMapper();
    }
    
}

Code:

translations :: id, verse_id, ...
(scrap translations_verses)

class Translation extends DataMapper {
  
   var $table = 'translations';
   var $has_many = array();
   var $has_one = array('verse');
  
    function Translation()
   {
      parent::DataMapper();
    }
    
}