[Solved] multiple/chained many to many join

[Daddy-O]

Hi,
I have a supposedly standard question but I am a bit in a hurry and while I know my SQL to join a set of normalized tables I am a beginner with Fabrik.
I don't understand how I do chained many to many joins with Fabrik.
Let me explain what I'm looking for:
I have three main tables, lets say categories, sections and books.
I need to have
?a form that enables me to insert book records and at the same time let me select multiple options of categories and sections related to the book I am inserting
?a front end view that I can filter on categories and sections so I only get the relevant books shown
Now in standard SQL this would need 4 joins:

SELECT * FROM books b
INNER JOIN (books_x_sections
INNER JOIN (sections
INNER JOIN (sections_x_categories
INNER JOIN categories ON sections_x_categories.cid = categories.id)
ON sections.id = sections_x_categories.sid)
ON books_x_sections.sid = sections.id)
ON books.id = books_x_sections.bid)

From the tutorial video example with two tables joined by many to many I understand that you need to use a crosstable with fk's and you can join a joined table on this crosstable
but how to get it done with three tables like above and get the form and front end view I need I don't understand.
Please somebody give me an explanation how to get this done.

 

[gizycki]

Perhaps (just a suggestion) try to filter the outputs with

prefilter

.

 

[Daddy-O]

To narrow down the results like a WHERE clause yes,

but the question is how I can get the joined content of all three tables in one output (which is supposed to be a list I guess).

Now I could throw overboard the requirement of multiple parents by using one-to-many relations with cascading dropdowns but that would be a poor solution.
If a cascading dropdown could be repeatable that would be a workaround but I can't see how to do that. Anyone?

 

[Daddy-O]

Ok, so now I know from http://fabrikar.com/forums/index.ph...licate-data-in-form-solved.35242/#post-176691
that I can join a list/table on multiple crosstables but then you get unrelated single many-to-many-joins instead of chained/interrelated many-to-many joins so I still miss a jigsaw piece.

 

[Daddy-O]

Here's a better version of my m : n example:

SELECT * FROM subjects
INNER JOIN (subjects_x_themes
INNER JOIN (themes
INNER JOIN (themes_x_books
INNER JOIN books ON themes_x_books.book_id = books.id)
ON themes.id = themes_x_books.theme_id)
ON subjects_x_themes.theme_id = themes.id)
ON subjects.id = subjects_x_themes.subject_id)

So a subject can be registered under more than one theme and a theme can occur in more than one book.


What I need:
1. form solution for input of subjects with an Add button for multiple books_x_themes relations:

form: Subjects
__________________________________
dbjoin dropdown Books > cascaded dropdown Themes [+][-]
__________________________________

2. output of a list on the front end of all subjects belonging to a specific theme belonging to a specific book

 

[Daddy-O]

In my current version I did not use the crosstables categories_x_themes and themes_x_books to have a version of the application based on cascading one-to-many.
Okay so if I'm right I could do the form part with a repeatable group with dbjoin on categories and the cascading dropdown on themes.
Now I get an SQL error which shows Fabrik is looking for an element in a wrong table.
I tried it again with a clean database, just for sure, and got it working.
Okay so I tried to create a new book with two different categories and themes and Fabrik creates automatically the crosstable books_8_repeat, which contains two records for the same book.
So this is how Fabriks works is it? That's not clear enough in the descriptions I'd say.

I guess this is pretty much what I needed at the back end.

Okay, so I renewed my copy of the list for front end view.
I use a separate list for the back end because there I want to hide a description field in the list which is taking to much space, but that is definitely something I want to show at the front end.
Unfortunately there's no option to set the visibility of an element for more than one template (back end or front end), so I don't see another way to get this done.

Finally I call the list in the front end with the string

{fabrik view=list id=8 books_8_repeat___theme=5 layout=accordion}

and there it is.

Hopefully other Fabrik newbies will benefit from my explanations.

 

[rob]

Okay so I tried to create a new book with two different categories and themes and Fabrik creates automatically the crosstable books_8_repeat, which contains two records for the same book.
So this is how Fabriks works is it? That's not clear enough in the descriptions I'd say.

hi sorry to come to this late after you've already worked things out. The automatic creation of the cross table is pretty new, which probably explains why its not clear or well documented that that happens.
Previously up to 3.0 we had some really crazy stuff going on for repeat groups - storing the data in a single field as csv strings, if the group was set to repeat from the group's admin page, but we at the same time allowed for cross tables if the user created them via joins. That method should still work, and is useful when you already have the list's set up for the tables you want to join to as:

1. You can use a less generic cross join table name
2. Your element settings will be copied across to the newly created group.

However, its not a simple to create the joins as to simply set the group to 'repeat' and let fabrik create the cross reference table behind the scenes.

 

[Daddy-O]

However, its not a simple to create the joins as to simply set the group to 'repeat' and let fabrik create the cross reference table behind the scenes.

Right, I can imagine that.
With the complexity of back end Joomla I was really glad to have found Fabrik to help me with a project I have to finish.
It's that I'm used to code everything by hand in other languages than PhP and it took me a while before I understood the Fabrik procedure.
Still it wouldn't be bad to have a more elaborated manual on joining multiple tables.