Table with two columns FK to the same table

[Jeremy Curny]

Hello,

I use NotORM on my website, but I have a question,

This are my DB tables :

TABLE USER (id, nickname)
TABLE MESSAGE (id, USER_id_sender, USER_id_receiver, message)

How can I configure my NotOrm for fetch all message (just received for the example) ? And how to fetch them ?

Thanks,

Jeremy

[Allysson David]

Best way to do this:

1. Change `USER_id_sender` to `sender_id` and `USER_id_receiver` to `receiver_id`;

2. Extend your Structure:

class myStructure extends NotORM_Structure_Convention {
    function getReferencedTable($name, $table) {
        switch ($name) {
            case 'sender':
            case 'receiver':
                return parent::getReferencedTable('user', $table);
        }
        return parent::getReferencedTable($name, $table);
    }
}

3. Use the your Structure when you create the NotORM object:

$db = new NotORM($pdo, new myStructure());

4. Make your queries as if you had a `sender` and a `receiver` table.

This will solve the problem when you're going from the `message` table to the `user` table via one of those columns (could have used the ->via() method too, but it could get tiring?)

Now, how to go from `user` to `message`? It will mainly depend on how you're coding. I'll post a possible solution in a bit (kinda busy atm), if you have any requirements about how you want to do that please reply to this.

Here's how I'll be suggesting to be done:

$messages_received = $db->user[$user_id]->messages_received();

[Allysson David]

So, turns out I exaggerated things a bit. LOL.

To get all the messages sent by a given user, just do this:

$messages_received = $db->user[$user_id]->messages()->via('sender_id');

What the extend class allows is this:

$sender = $db->message[$message_id]->sender;

$receiver = $db->message[$message_id]->receiver;

[Jeremy Curny]

Thank you for your reply,

Now I see clearer,

Do rename columns is mandatory? I'd rather avoid if possible :)

[Allysson David]

Well, no.

The ->via() will accept whatever column name you use but you will lose some other functions (ie, accessing user's table from message's sender/receiver column with ->sender/->receiver) if you don't follow a pattern for foreign key naming.

Usually the default pattern is `table_id` (but you are using `something_id_something`), unless you customized your Structure Convention or you are using Structure Discovery, in which case it should work with any name you use.

Give it a try and if you have any problems we can look for a workaround.

[Jeremy Curny]

Thanks for all, this code working well :

<?php

error_reporting(E_ALL);

require __DIR__ . '/vendor/autoload.php';

$pdo = new PDO('sqlite::memory:');

$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

$pdo->exec('

CREATE TABLE USER (

id INT UNSIGNED NOT NULL PRIMARY KEY,

nickname VARCHAR(63) NOT NULL

);

CREATE TABLE MESSAGE (

id INT UNSIGNED NOT NULL PRIMARY KEY,

USER_id_sender INT UNSIGNED NOT NULL,

USER_id_receiver INT UNSIGNED NOT NULL,

content TEXT NOT NULL,

FOREIGN KEY (USER_id_sender) REFERENCES USER(id) ON DELETE CASCADE,

FOREIGN KEY (USER_id_receiver) REFERENCES USER(id) ON DELETE CASCADE

);

INSERT INTO USER(id, nickname) VALUES (1, "jeremy");

INSERT INTO USER(id, nickname) VALUES (2, "roger");

INSERT INTO USER(id, nickname) VALUES (3, "frank");

INSERT INTO MESSAGE(id, USER_id_sender, USER_id_receiver, content) VALUES (1, 1, 2, "yo");

INSERT INTO MESSAGE(id, USER_id_sender, USER_id_receiver, content) VALUES (2, 2, 1, "yo !");

INSERT INTO MESSAGE(id, USER_id_sender, USER_id_receiver, content) VALUES (3, 1, 2, "yeah !");

');

class myStructure extends NotORM_Structure_Convention {

    function getReferencedTable($name, $table) {

        switch ($name) {

            case 'USER_id_sender':

            case 'USER_id_receiver':

                return parent::getReferencedTable('User', $table);

        }

        return parent::getReferencedTable($name, $table);

    }

}

$NotORM = new NotORM($pdo, new myStructure());

$Jeremy = $NotORM->USER()->where('nickname=?', 'jeremy')->fetch();

$Messages_sended = $Jeremy->MESSAGE()->via('USER_id_sender');

$Messages_received = $Jeremy->MESSAGE()->via('USER_id_receiver');

$Messages_all = null; // How to join sended and received ?

echo "USER";

echo '<pre>' . print_r(iterator_to_array($Jeremy), true) . '</pre>';

echo "Messages sended";

echo '<pre>' . print_r(array_map('iterator_to_array', iterator_to_array($Messages_sended)), true) . '</pre>';

echo "Messages received";

echo '<pre>' . print_r(array_map('iterator_to_array', iterator_to_array($Messages_received)), true) . '</pre>';

Just a last thing, do you know how can I join the sender and received messages (while selecting with notOrm) ?

Thanks again !

[Allysson David]

The obvious solution should have been using ->union() but for reasons I don't understand it isn't working for me when I try.

(It's only showing the results from the first set, if I figure out why I'll send an update.)

So, for now, I'd suggest you build the query manually since you already have a variable with user data:

$db->messages()->where('sender_id', $jeremy)->or('sender_id', $jeremy);

[Allysson David]

I figured out why the ->union() doesn't show all the results but not to fix.

Just to share:

When using UNION it will identify the specified keys from the ->via() as being the "main column".

That way, the first part of the ->union() has `sender_id` as the "main column" and the second part will have `receiver_id` as the "main column". Since both are specified to be the same as the `user_id` then, somewhere inside it, NotORM will skip additional rows where the "main column" has been iterated over.

Setting the second param of ->union() to true doesn't fix. It's either something from the PDO or NotORM itself.

Will update if I figure out a way to fix.

[Allysson David]

NotORM_MultiResult's fault apparently.

->union() will only work if you don't use ->via() apparently.

You can either go with the ->where()->or() I suggested before or:

$sent = $db->message()->where('sender_id', $jeremy);

$received = $db->message()->where('receiver_id', $jeremy);

$all = clone $sent;

$all->union($received);

Explanation on clone:

If you don't clone the first object before using the ->union() it will apply the union to it too.

Remember that NotORM applies the changes and returns itself so that it could be chained.

Without the clone then $sent would have the all the messages of that user instead of only the sent ones.

[Jeremy Curny]

Hi Allysson David,

Thanks for your reponse and your help, 

For my case, the OR is perfect.

I've a last question.

Now I try to get the user sender of a message, like this : 

$db = new NotORM($pdo, new myStructure());

$MESSAGES = $db->MESSAGE()->where('USER_id_receiver=?', 1);

foreach ($MESSAGES as $MESSAGE) {
 $USER = $MESSAGE->USER()->via('USER_id_sender')->fetch();
 // TBD
}

And i get this error : SQLSTATE[HY000]: General error: 1 no such column: USER.USER_id_sender

I change my structure by this :

class myStructure extends NotORM_Structure_Convention {
    function getReferencedTable($name, $table) {
        switch ($name) {
            case 'USER_id_sender':
            case 'USER_id_receiver':
                return parent::getReferencedTable('User', $table);
        }
        return parent::getReferencedTable($name, $table);
    }

    function getReferencingTable($name, $table) {

        switch ($name) {
            case 'USER_id_sender':
            case 'USER_id_receiver':
                return parent::getReferencedTable('User', $table);
        }
        return parent::getReferencedTable($name, $table);
    }
}

Do you see where is my problem ?

Thanks in advance,

Have a nice day.

[Allysson David]

That's because the column names don't follow the 'pattern' of the structure. That's why it was the first change I suggested when I replied before. The ->via() only works if you follow the pattern in that case, which `user_id_sender` don't.

Also, the custom Structure would allow something like:

$USER = $MESSAGE->sender;

If you had followed all the changes.

If you want to get the sender/receiver of a message without changing the column name, then just do this:

$USER = $db->USER[$MESSAGE->USER_id_SENDER];

Remember:

It is not necessary to keep that custom Structure if you don't change the column names to fit the pattern!