Which database joins are supported and how?

[batonac]

I'm designing a large database using MySQL workbench that I'd like to use as the back end for a Fabrik application. I'd like to make sure that my table joins are compatible with Fabrik, but I can't seem to find much documentation or forum discussions specifically on this topic...

Firstly, are one-to-one joins compatible with Fabrik? I'm thinking two tables such as 'school' and 'school_mission_vision' that share the same primary key (entries from the second table are identified by the auto-incrementing primary key in the first table). I see this layout as beneficial for storing additional data for a table that does not need linked to every list where that table is used. Will Fabrik be able to understand/enforce this structure?

Secondly, what is the total capacity of Fabrik in regards to many-to-many joins? I understand that a multi-select dropdown implements a many-to-many join... Is that the only element available for this function? Ultimately I'd love to see the ability to use check boxes, pick-lists, even sub-lists embedded in forms, to specify my many-to-many associations.

When I create many-to-many joins in Workbench, for example 'student' and 'class', a new table is created with schema student_has_class containing student_id and class_id. Are these tables/schema supported by Fabrik? Or will I have to delete these tables and recreate the many-to-many joins within Fabrik?

 

[rob]

Hi
One to one joins should work. Create the list pointing at the school db table, then edit it to add the join and ensure that the newly created join's group is not set to be repeatable.

The dbjoin element has a render as checkbox option which works for many-many joins, but there aren't any other elements which currently support many-many joins.

For your student_has_class table fabrik produces its own 'linkage' table when you create the multi-select dbjoin element, so you would need to remove those tables and let fabrik create them for you.

An alternative could be to join your students table to the student_has_class table (setting the student_has_class group to be repeatable), and set the student_has_class.class field to be a dbjoin element with the "front end select" option turned on. This would enable users to open a list of classes in a pop up window and select which one would be used for the field's value.

If you are more concerned with simply showing data rather than editing / adding it then there are a couple of other ideas that spring to mind:

* join the student_has_class table with the classes table, the use the related data option in the classes list to provide a link to show the student's classes/

* join the student_has_class table with the classes table, then in the student's form add a {fabrik view=list id=X student_had_courses___student_id=[rowid]} in the form's outro/intro text to display a list of the students courses

 

[batonac]

Rob you're a star! You've answered my questions and more.

I will need some special tricks for both adding/editing & displaying my data, and you have me off to a great start. I was really hoping for a way to do many-to-many beyond the multi-select dropdown... your "front-end select" popup idea sounds like it will do the trick.

Thanks!

 

[cheyennemtnman]

Hey Rob (Et al),
I created form/table with a group with a Supervisor's Name and a repeating group of Employees names assigned to that supervisor.
Each Supervisor Group record has 1 - 8 repeating Employee Group records with the employees that are a direct report to the Supervisor.
I just want to list the Employees to the Supervisor can select the employee name in a dropdown in the databasejoin element.

Is this possible in Joomla 3.2 and Fabrik3.1.rc2?

 

[cheesegrits]

Can you start your own thread for this, rather than adding to an existing thread that is not related?

I'm also not sure what you mean by:

I just want to list the Employees to the Supervisor can select the employee name in a dropdown in the databasejoin element.

When you post your own thread, can you explain what you need in a little more depth?

-- hugh