SQL element filter in lists

[jsg2510]

I have a user element that is populated using juser as shown in the fabrik intro video (on homepage). At the joomla user level I have added a new group for school-class.

The user appears as a drop down in many lists

I want to add a filter in the element so that it figures out the school class of the logged in student and in any given list it only offers the names of other students in the class.

Example - who do you want to sit next to? - will offer a multiselect list of kids in the same class but not kids in other classes.

I tried the following:

Select * from cnr_users
Where Class in (select Class from cnr_users Where user = {$my->id})

Thanks

 

[Bauer]

You might want to try using a cascading dropdown to accomplish that... http://fabrikar.com/forums/index.php?wiki/cascading-dropdown-element/

 

[jsg2510]

thanks
can't quite figure it out...
my user is an element (actually a database join) and so it doesn't have a list (on the data tab) - so I don't think that I can use this option?

Any further ideas?

 

[Bauer]

OIC now. Ughh.

By 'added groups' for every class name I assume you mean Joomla user groups? And you assign the user to a Joomla usergroup when the record is created? (Or to multiple 'class' groups?)

I'd probably suggest using a table rather than Joomla user groups for the classes. But I'm still trying to get 'the big picture'.

Here's what I 'see' or vision...
You have a table intended to contain Students info, (right?)
Each student is also a Joomla user. (right?)
And (you will have) a table that includes Classes.
Links to both the joomla users table and the classes table are included in the Students table as database join elements - and both are configured with the 'Add option on front end' set to Yes. (So that as you add new records to the Students list you can either select from the jusers table or add a new user (student) to the jusers table via the dbjoin element. And the same with the classes. You then select the class (or classes?) for that student and can add new classes 'on the fly' from that form too.

That way you have (links to) both the student's Joomla user_id and the classes by id in one table (though for neither of them is the actual data stored in that table - just the FK to the Joomla users and the classes table.)

Now the big question is... Is this for grade school where each student has only one class, or high school where students have multiple 'classes'? Because one is simple to do - and the other, not so much.

 

[jsg2510]

Hey Bauer thanks for taking the time on this one - help is really appreciated

I hadn't thought of a classes table - but no reason why it wouldn't be a good option

So now for the bad? news -

A student can only be in a single class (say Grade 11c) - this is their home in school but
Studies multiple subjects (history, english etc) and can also
have many assignments and can have shared assignments

So each student can only see other students in their class - there is a list of subjects that leads to an assignments form (in that subject) & the assignments can be shared - so when allocating an assignment it belongs to a subject and the multi-select student list is only students in the same class (if Alan allocates an assignment to Alan & Bill then it shows up for both of them when they look for assignments that they need to do)
Thanks

 

[Bauer]

The reason I asked that is I was thinking you could do something like this...
I'm assuming that the list/form where you want to include the "Who do you want to sit next to?' element (showing classmates) is this same 'Students' table - I'll just call that the 'students' table.

Set both of the dbjoin elements used in the students form - 1. juser (e.g. element name: 'user_id') - and 2. class (e.g. element_name: 'class_id') - (or any elements you don't want the student to be able to change), so that only 'Special' admin users can change/edit them.

Then add a new databasejoin element to the 'students' list/group.
Name it 'classmates' and make the label 'Who do you want to sit next to?'
Configure this new dbjoin element as a dropdown list using 'data' from the same Students table (students).
Set the key to 'id' and the label to 'name' (Student/Joomla user name)
Set the Where condition to "WHERE {thistable}.class_id = {students___class_id_raw||0}"

(The ||0} (or zero) part at the end of the placeholder will prevent the code from throwing an error when you add new records and the class_id is not yet set.)

That should populate this new dbj dropdown list with all students who have the same class_id as the student record being edited.

And it should work so that when you (as an 'Special' user/admin) add a new record you will see the list of 'classmates' in this datbasejoin element change as you select any juser from different classes. (But it won't change for a student because they can't change the student name)

You could use similar element for the other things you mention. Be sure to test and play around with each new element though, because the databasejoin element can be a bit quirky in that it will work one way for a single-select dropdown or radio button - and another way if formatted to display as multiselect or checkboxes.

And I'm just thinking - don't forget to always save that form with that juser/dbjon element set to the user you intended to add/edit! - For that matter, it's probably best to use the 'are uinique values' element validation plugin to prevent saving duplicate jusers.

 

[jsg2510]

thanks - working on it

 

[Bauer]

thanks - working on it

I just realized I forgot to mention that for this to work 'in real time' (as you change the selected student the classmates will change) there are 2 things required...
1. A recent version of the databasejoin element plugin (I think it's been about 3 weeks since the feature was added)
2. The 'Ajax update' setting in the 'Data - where' tab must be set to 'Yes'.
Hope this works for you.

BTW, this is really just another (easier to understand) way of creating a cascading dropdown. You may need to use a cascadingdropdown element instead for any elements where you need the resulting selection to be multiselect - because the new 'Ajax update' feature of the dbj only works for (single-select) dropdowns.