Extending one-to-many join with another table in the middle

bilyejd

I'm trying to extend the one-to-many join example and manual page to match my database structure. I have an intermediate table in order to provide an inheritance mechanism to the database. I have a main 'tasks' table (similar to 'users' in the example), a 'task_application_types' table (similar to the 'user_permission' table), and an 'application_types' table (like the 'permission' table).

Below is a representation of a portion of my database from MySQL Workbench:

I have also created a SQL Fiddle that has a small dataset populated.

With this structure, I am able to create tasks that have a single Sel_Application_TypeID, which is used to create a specific set of Application_Type_Names for the task using the task_application_types linking table. In this way I can clone tasks or make child tasks, and still have the same selection for Application_Type_Names. This structure seems to work well for the rest of my site, but I'm struggling with how to get DataTables to show a comma separated list of the Application_Type_Names, like the Permissions column in the example. I'm sure I need to use Mjoin, and the example makes sense, but how can I incorporate the additional table?

Thanks,
Jordan

allan

Hi Jordan,

Am I correct in assuming that you are trying to show the "tasks" in the client-side table to the end user?

I'm afraid that the Editor pre-built Mjoin won't work in this case as it can't join to an extra table itself. Ideally it would do a left join to your application_types, but that is one step beyond its current capabilities I'm sorry to say.

However, what you could perhaps do is to a SELECT on the application_types, get the ids and names and include that in the JSON data returned to the client-side. Then you'd need to use a rendering function to look up the name based on the id and display that.

Its not ideal perhaps, but I think that should still be possible. Sounds like a good topic for a blog post in fact.

Allan