DataMapper ORM for Codeigniter Relations

Question

I have a table ...

CREATE TABLE IF NOT EXISTS `messages` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `to` int(11) NOT NULL,
  `from` int(11) NOT NULL,
  `subject` varchar(50) NOT NULL,
  `message` varchar(1000) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=5 ;

To and From is the primary key id from Users Table.

How can I get the user details when I get each message with CodeIgniter DataMapper.

Answer 1

You are missing a couple key points to using the DataMapper for CodeIgniter. First off you need to do some pretty simple and important things. DataMapper (DM) uses normalized db naming to find relationships. What this means is if you query your db. Now it's a little harder to use DM for two columns and I think that you really don't need it.

First if you don't use DM you really only need two queries

SELECT u.*, m.* FROM messages AS m, users AS u WHERE m.from = u.id AND m.id = SOME_ID.

This query will get you all user details and message details for some message ID. Now this is semi-simple case because I am assuming a message can only be from one user.

For the to field on the other hand I will assume you should use a relational table. To use DM for it you have to name the table something like users_messages but again why do you need to use DM when it really is overkill.

Now for the from field you have a many to many relation because a message can have many users that it was to and a user can have many messages that they sent.

So create a table like this

CREATE TABLE message_to (
    user_id BIGINT UNSIGNED NOT NULL,
    message_to_id BIGING UNSIGNED NOT NULL,
    PRIMARY KEY (user_id, message_to_id),
);

If you want to do it right you will also use foreign keys but that depends on your DB

Now you can query really easily and get all the users a message was sent to.

SELECT u.*, m.* FROM users AS u, m AS messages JOIN messages_to AS m_t ON (u.id = m_t.user_id)

And querying the other way around is just as easy, getting all the messages a user has sent.

Remember just because a tool like DM exists doesn't mean it is the best tool for the job and actually using DM in this case incurs a pretty decent overhead when it is not necessary.

Doing this with DM would require the same things you just cannot name your tables/columns as you see fit and you need a class for every table creating it's relationship with other tables.

Meaning you have a lot of extra work to use DM, and you need to learn their syntax.