Editor using Mjoin for three tables

Balubaer

Hi everybody,

I'm a little bit confused about the usage of the Editor's Mjoin-Feature and if it is possible to use it to join three tables together - or what will be the best practice to implement it?!

I have the three tables

+-----------------+     +-----------------+     +-----------------+
| projects        |     | target          |     | status          |
+-----------------+     +-----------------+     +-----------------+
| id              |     | id              |     | id              |
| name            |     | name            |     | name            |
+-----------------+     +-----------------+     +-----------------+

This will be joined in a table

+-------------------------+
| projects_targets_status |
+-------------------------+
| project_id              |
| target_id               |
| status_id               |
+-------------------------+

When I tried to join the three tables to the bottom one (using the link function) I'll get the error, that link can only be used twice.

So I'm wondering if there's a way to get an editor in which it is possible to create/edit/delete entries for the latter table.

Here's the code I tried to use:

$editor = Editor::inst($db, 'projects')
    ->debug(true)
    ->field(
        Field::inst('projects.title')
    )
    ->join(
        Mjoin::inst('portals')
            ->link('projects.id', 'projects_portals_states.project_id')
            ->link('portals.id', 'projects_portals_states.portal_id')
            ->link('states.id', 'projects_portals_states.state_id')
            ->order('name')
            ->fields(
                Field::inst('id')
                    ->validator('Validate::required')
                    ->options(Options::inst()
                        ->table('portals')
                        ->value('id')
                        ->label('name')
                    ),
                Field::inst('name')
            )
    )
    ->join(
        Mjoin::inst('states')
        ->link('projects.id', 'projects_portals_states.project_id')
        ->link('portals.id', 'projects_portals_states.portal_id')
        ->link('states.id', 'projects_portals_states.state_id')
        ->order('name')
        ->fields(
            Field::inst('id')
                ->validator('Validate::required')
                ->options(Options::inst()
                    ->table('states')
                    ->value('id')
                    ->label('name')
                )
        )
    )
    ->process($_POST)
    ->json();

Any help is appreciated.

Regards,
Dennis

Balubaer

Hi there,

does really no one have an idea how to handle this situation?

The only alternative I see is to denormalize the table an merge two tables (platforms and states?) together but I'm not very comfortable with this situation.

So, if anyone has an idea I'll appreciate that.

Thanks,
Dennis

allan

Hi Dennis,

I missed your original post - sorry about that. I'm sorry to say that at the moment Editor's MJoin does indeed only work between two tables. Abstracting it to 3 or n tables is actually not something that has been requested before so its not something I've thought about I'm afraid.

One option would be to split the projects_targets_status into two, and then use two MJoin statements with project_id as the common column between them. The SQL schema isn't quite as nice, but it would work with Editor's libraries that way, and I can't immediately see any other downside.

Allan

Balubaer

Hi Allan,

thanks for your hint. Unfortunately this will not work as needed, because the state is dependent on the portal, which is a one-to-many relation to projects.

One project can have many targets, each of them will have one state.

I already tried to split the projects_targets_status table into two tables.

+------------------+    +-------------------------+
| projects_targets |    | projects_targets_states |
+------------------+    +-------------------------+
| id               |    | project_target_id       |
| project_id       |    | state_id                |
| portal_id        |    +-------------------------+
+------------------+

But the problem is that I also cannot (left)join the projects_targets_states.project_target_id with projects_targets.id because Mjoin don't know ->leftJoin().

Maybe you'll have another idea how to handle it?

The only way I see is to denormalize all tables and put all into one table without references, but that is absolute no good database layout.

Greetings,
Dennis

allan

because Mjoin don't know ->leftJoin().

Yes, that is a frustrating limitation that we keep bumping up against. Let me look into how difficult it will be to implement and I'll get back to you.

Allan

allan

I'm been thinking about this of the course of the day and realised that even with the left join from Mjoin working, with this setup you'd probably want to be able to modify information in the Mjoined / left joined tables, and I think that would get complicated.

Instead, how about something like this? The parent table would list the projects and clicking it would load the one-to-many data for that selected row, which is easy to include left joined data, since it is just a standard table with a where condition applied to it.

Allan

Balubaer

Hi Allan,

at least I got back to a similar solution. But thanks for your effort.

Greetings,
Dennis