advanced many-to-many databasejoin filtering SOLVED

[batonac]

Please forgive me if this is covered elsewhere in the forum and/or documentation...

I'd like to do some advanced databasejoin filtering which I'll use a hypothetical scenario to describe:

I've already discussed a scenario where two tables, 'student' and 'class' are many-to-many joined by a third table: 'student_has_class'.

Now I'd like to filter the join list in the fabrik form so that only certain classes are available to be assigned to students. Let's say there's a third table called 'study_track' and both the 'student' table and 'class' table contain a 'study_track_id' field/element. How do I filter the 'student_has_class' databasejoin so that only classes with the same 'study_track_id' are available?

Also, if we take this a step further, and create a many-to-many join between 'class' and 'study_track' using a table called 'study_track_has_class', how can we create the same filter as above based on that join?

 

[rob]

Hi

I'm not sure which suggestion you implemented from that thread which means I'm not sure exactly what suggestion to make here, but I'll have a go.....

I'm presuming your student_has_class linkage, is a database join element set to render as a checkbox, and it points to a table called 'classes'

Let's say there's a third table called 'study_track' and both the 'student' table and 'class' table contain a 'study_track_id' field/element. How do I filter the 'student_has_class' databasejoin so that only classes with the same 'study_track_id' are available?

So I've written down what I think is the table structure:

+----------------+
| students
+----------------+
| user_id
| study_track_id
+----------------+
 
+----------------+
| classes
+----------------+
| id
| study_track_id
+----------------+
 
+----------------+
| student_has_class
+----------------+
| student_id
| class_id
+----------------+
 
+----------------+
| study_track
+----------------+
| id
+----------------+

Then database element has a 'where' field which you can use for this purpose. What you would need to do is have a sub query in there, something like....

WHERE {thistable}.study_track_id = (SELECT study_track_id FROM students WHERE user_id = {$my->id})

{thistable} gets replaced with the alias that the database join element uses for the table 'classes'

 

[batonac]

The sub-query solution is brilliant. I should have thought of that...

From what I understand the '{$my->id}' in your query is meant for an association between the current Joomla user and the student. This is cool, but not quite my use case...

How can we create a similar query where no association between the current Joomla user and 'student' exists? In other words, filtering should be done by the current 'student' form entry being edited, not by the current user. (think administrative control panel, not student control...)

As always, thanks Rob!

BTW, I should have specified; the original solution I'm referring to is this:

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.

You did a pretty good job of nailing down the table structure, all except for students.user_id...

 

[cheesegrits]

I think you could just replace {$my->id} with a placeholder for the user_id value you need from the main table the join is on. I've gotten a little confused as to which table this join is on, but it'll be something like {students___user_id}.

-- hugh

 

[batonac]

I did not know you could call element values like that. Very cool! I'll definitely develop this and report back on my mileage.

 

[cheesegrits]

Yup, we do placeholder replacement on the WHERE filters in joins and CDD's.

Thing you have to watch for is on new forms, where your placeholders won't have values. And I can't offhand recall if they will have their default values at the point the WHERE filter is built. But on edit, element placeholders should have their expected values.

-- hugh

 

[batonac]

Thing you have to watch for is on new forms, where your placeholders won't have values. And I can't offhand recall if they will have their default values at the point the WHERE filter is built. But on edit, element placeholders should have their expected values.

This makes sense. I'll probably just have to include a message in the form telling the user to save the new entry before they input data into any of these many-to-many joined groups...

 

[cheesegrits]

OK.

If you don't mind, I'd appreciate some test input. I'm interested as to whether the WHERE filter takes element defaults in to account in placeholders, when running on a new form. I could test myself, but I'm way too lazy.

-- hugh

 

[batonac]

I'm still testing the functionality of this solution. So far I know that on a new form the placeholder doesn't return anything by default. I've created a workaround by placing the placeholder in an IFNULL clause, but I still have to verify the functionality:

WHERE {thistable}.subject_id = (SELECT subject_id FROM unit WHERE id = IFNULL('{unit___id}',0))

 

[cheesegrits]

OK. I really should fix it so placeholders have default values when creating a new form, as that would make this kind of thing a lot easier.

-- hugh

 

[rob]

that already exists - I've made that clearer on the wiki page -
http://fabrikar.com/forums/index.php?wiki/placeholders/#default-values

So I think you can do:

WHERE {thistable}.subject_id = (SELECT subject_id FROM unit WHERE id = '{unit___id||0}')

 

[cheesegrits]

Ah, I meant so they would automagically pick up the element defaults themselves. Although that placeholder | trick is a useful feature.

-- hugh

 

[batonac]

This is all great advice.

I think an even better solution than default or substitute values would be to use a cascading dropdown to filter the options. My only problem is that CDD doesn't have a 'front-end select' option, which is pretty useful for navigation in my large data set. Is there anyway around this?

 

[cheesegrits]

Not really. It's something we've thought about adding, but just haven't had the time.

-- hugh

 

[batonac]

I understand. Is there any easy way to make the form save and reload whenever relevant elements are set or altered?

 

[batonac]

On second thought, this would create a few issues because of required database fields that may not yet be filled when the save is triggered... I suppose that leads me try to recreate the front-end select and/or add buttons independently. Can this be done without too much trouble?

 

[batonac]

So I think you can do:

WHERE {thistable}.subject_id = (SELECT subject_id FROM unit WHERE id = '{unit___id||0}')

I've been running into issues when using filters like this in combination with front-end select. I can add a new entry through front-end select that *would* be compatible with this filter, but it doesn't show up in the drop-down. When I reload the form, the new entry is then accessible in the drop-down, but not before. Could I get this fixed? Is there a workaround?

 

[batonac]

Nevermind. This thread has been branching out all over the place. I'll mark it solved and open up separate bug reports.