FuelPHP Forums

tarlyun

How to implement many-many-many relationship.
Here synthetic example: User can upload many foto. User can mark another user on foto. One foto can contain many users. Many-many.
User belong to one or many groups. Group can contain many users. Many-many.
Group -> User -> Foto SQL for CREATE TABLE:

CREATE TABLE user 
(
 id int, 
 name varchar(20)
);

INSERT INTO user (id, name)
VALUES
(1, 'Alex C.'),
(2, 'Max T.');


CREATE TABLE group 
(
 id int, 
 name varchar(20)
);

INSERT INTO group (id, name)
VALUES
(1, 'Admin'),
(2, 'Moderator'),
(3, 'User');


CREATE TABLE foto 
(
 id int, 
 name varchar(20),
 filename varchar(50)
);

INSERT INTO foto (id, name, filename)
VALUES
(1, 'My PC', 'my-pc-2012.jpg'),
(2, 'Friends', 'P1000766.JPG'),
(3, 'Friends Again', 'P1000767.JPG');


CREATE TABLE group_user 
(
 group_id int, 
 user_id int
);

INSERT INTO group_user (group_id, user_id)
VALUES
(1, 2),
(2, 2),
(3, 2),
(1, 3);


CREATE TABLE foto_user 
(
 foto_id int, 
 user_id int
);

INSERT INTO foto_user (foto_id, user_id)
VALUES
(1, 1),
(2, 2),
(3, 2),
(3, 1);

We want show all admins fotos.
Plain SQL:

SELECT group.name, user.name, foto.name, foto.filename
FROM group
LEFT JOIN group_user ON (group.id = group_user.group_id)
LEFT JOIN user ON (user.id = group_user.user_id)
LEFT JOIN foto_user ON (user.id = foto_user.user_id)
LEFT JOIN foto ON (foto.id = foto_user.foto_id)
WHERE group.id = 1;

group.name | user.name | foto.name | foto.filename
Admin | Max T. | My PC | my-pc-2012.jpg
Admin | Max T. | Friends Again | P1000767.JPG

Questions:
1.How get all admins fotos?
2. What about SQL perfomance?
3. Can somebody share their code with many-many-many relationship?

Harro

the ORM doesn't work this way, so if you want a result like in your plain SQL example, you will have to code your own query. The ORM (as the name implies) works object based. Create the models for each of the tables, and define the correct relations between them. How is documented in the manual (for example http://docs.fuelphp.com/packages/orm/relations/many_many.html for a many to many relation). Once you've created the models you can do this

// get the info
$group = Model_Group::find()->related('user')->related('user.foto')->where('id', '=', 1)->get();

// output some data
echo $group->name;
foreach ($group->user as $user)
{
    echo "Foto's of user:", $user->name;

    foreach($user->foto as $foto)
    {
        echo "* ", $foto->name, $foto->filename;
    }
}

FuelPHP Forums

Ask your question about FuelPHP in the appropriate forum, or help others by answering their questions.

Howdy, Stranger!

Categories

In this Discussion