Multi-join tables

[jonyhayama]

Hi, I'm having some trouble working with this.

Please consider the following database structure:

user (id INT AUTO_INCREMENT, name VARCHAR(32))
message (id INT AUTO_INCREMENT, user_from_id INT, user_to_id INT, text TEXT, created_at TIMESTAMP)

Let's say I'm trying to create a simple message system. One user can send a message to another user. Now let's say I'm creating some sort of admin area so system administrators can see all messages, who sent them and who they were sent to (I know it's not a very common thing to do, but I'm using this example because I think it's easier to understand =D).

When I $mapper->message->fetchAll() I get the the ids of both users (from and to), but not their names. I've tried to use $mapper->message($mapper->user, $mapper->user)->fetchAll() without success (the identifiers are not following the standard style). I've created a style like this:

<?php
use Respect\Data\Styles as respect;
class MessageStyle extends respect\Standard{
        protected $remoteIdentifiers = array(
        'user_from_id' => 'user',
        'user_to_id' => 'user2'
    );

    public function remoteIdentifier($name){
        $remoteIdentifiers = $this->remoteIdentifiers;
        $identifier = array_search($name, $remoteIdentifiers);

        return ($identifier) ? $identifier : parent::remoteIdentifier($name);
    }

    public function remoteFromIdentifier($name){
        $remoteIdentifiers = $this->remoteIdentifiers;
        if( array_key_exists($name, $remoteIdentifiers) ){
            return $remoteIdentifiers[$name];
        }

        return parent::remoteFromIdentifier($name);
    }
}

I was able to load the information of user_from_id correctly, but had no success on user_to_id. I've also noticed that the variable $name on the remoteIdentifier() method is set to have the table name, not the alias, therefore my routine wouldn't work as expected. I've latter added a counter to it and was able to return the proper identifier, but still wasn't able to get both user_from and user_to information on one database request.

Any suggestions?

Thank you in advance.

[williamespindola]

@jonathanhayama you need get from, to and name fiels?

Try this:

$mapper->massage->user->fetchAll();

See in documentations Joining

[jonyhayama]

Hi @williamespindola I did try this. It will only work if I have a field named user_id on my message table. I will also bring only the name of one user.

Basically I need something the following SQL:

SELECT * FROM message INNER JOIN user AS user_from ON message.user_from_id = user_from.id INNER JOIN user AS user_to ON message.user_to_id

If I do a simple join as explained on the documentation, I get something like

SELECT * FROM message INNER JOIN user ON message.user_id = user.id

Do you happen to have any other suggestions?

Thank you very much!

[williamespindola]

You need use Db class, This will work as well:

$db->select('*')
     ->from('message')
     ->innerJoin('user AS user_from')->on('message.user_from_id = user_from.id')
     ->innerJoin('user AS user_to')->on('message.user_to_id = user_from.id')
     ->fetchAll(array());

[jonyhayama]

Hi @williamespindola, thank you for your input. Your code definitely works. The thing is, I have to generate a new SQL statement then create the objects and set their values "manually". I was wondering if I could do it using the $mapper. I read on the documentation that tables that appear more than once on the same chain are numbered (ex. user, user2). Is there any way to access the data of user2? Thank you again and I'm sorry if I'm being too stubborn/stupid...:)

[williamespindola]

The user, user2, user3 it is alias for the tables. The best practice for your case is use Db class because yours keys are in a different style user_from_id, etc.

however to access user2 just: $mapper->user2

[alganet]

Note: I haven't tested these solutions, but I've used very similar ones and there are working samples in the tests!

If you can change the database schema, I would suggest something like this:

user (id INT AUTO_INCREMENT, name VARCHAR(32))
message (id INT AUTO_INCREMENT, text TEXT, created_at TIMESTAMP)
message_from (id INT AUTO_INCREMENT, message_id INT, user_id INT, user_id INT)
message_to (id INT AUTO_INCREMENT, message_id INT, user_id INT, user_id INT)

And then fetch like this:

<?php
// Create alias just once
$mapper->fullMessage = $mapper->message(
    $mapper->message_from($mapper->user),
    $mapper->message_to($mapper->user),
);

$mapper->fullMessage->fetchAll(); // All messages
$mapper->fullMessage[12]; // message.id=12
$mapper->fullMessage(array('message_from.user_id' => 10)); // .. etc

Another approach (more like a monkey patching) would be using views:

user (id INT AUTO_INCREMENT, name VARCHAR(32))
message (id INT AUTO_INCREMENT, user_from_id INT, user_to_id INT, text TEXT, created_at TIMESTAMP)
CREATE VIEW recipient AS SELECT * FROM user
CREATE VIEW message_recipient AS SELECT id, user_from_id AS user_id, user_to_id AS recipient_id, text, created_at FROM message

And then:

<?php
// Create alias just once
$mapper->fullMessage = $mapper->message_recipient(
    $mapper->user,
    $mapper->recipient,
);

$mapper->fullMessage->fetchAll(); // All messages
$mapper->fullMessage[12]; // message.id=12
$mapper->fullMessage(array('message.recipient_id' => 10)); // .. etc

That works because Respect\Relational works only with names. Styles can also work to fix that, but they need to translate the table "user" to both "user" and "recipient" and the "user_*_id" columns on the "message" table.

[jonyhayama]

Hi @alganet, the Views worked like a charm. Thank you very much! I'm sure that using an additional table will work as well. Regarding using the Db class as @williamespindola suggested, it also works, but then again, you'd have to run a few extra loops to get the objects the same way as Respect does.

[alganet]

Can we close this? Or perhaps we should make an entry on the docs with the approaches for new users?

[williamespindola]

Can close. Thanks