filter dbjoin checkbox

[pie]

Hi, I was wondering if it's possible to set up some "dynamic" filter (at least one) on the elements of a dbjoin element (checkbox - many to many).
I saw the prefilter option of the element itself, but as a prefilter it is applied on load and then then the displayed elements are "fixed".
It would be useful to my project that the user could update the filters on the run, as it happens on a standard list, but on the elements gathered by the dbjoin element.

Is there a working solution already?

Thank you

 

[troester]

Did you try the "Ajax update"? (It says "works only for dropdown joins" but maybe...)

 

[pie]

This may be exactly what I need, thank you! I am away from my laptop now but I'll try it asap.
Is there some documentation or example of use?

Thanks again

 

[cheesegrits]

Meh, I'm pretty sure I never extended that to work with checkbox joins. But try it and see.

-- hugh

 

[pie]

Yep I tried it, but I was unable to make it work even in a multi selection or single dropdown:
I wrote a {thistable}.filter = '3' inside the data-where Filter where textarea and enabled AJAX update
cleared cache of browser and website and reloaded browser
looking at the query with fabrikdebug I can only see the first part of the where query (no Filter where) and the output shows every entry.

Am I missing something?

I'd like the user to filter the joined values, or at least group them, because there are many entries in the joined table.
Even a workaround would help:
adding dbjoin elements pointing to the same _repeat table but prefiltered with different parameters could be an option, but I'd like to avoid the creation of other _repeat tables because I'd like to keep every relation in the same (bigger) one.
Is this possible?

Thank you

 

[cheesegrits]

I wrote a {thistable}.filter = '3' inside the data-where Filter where textarea and enabled AJAX update
cleared cache of browser and website and reloaded browser
looking at the query with fabrikdebug I can only see the first part of the where query (no Filter where) and the output shows every entry.

Well, first off, that won't trigger an AJAX update. You have to use a placeholder to get that to work. If you enable AJAX update, we scan your WHERE clause for {tablename___elementname} placeholders, then in the join element JS on the form, we add events to watch any elements you are using in the WHERE clause, and trigger an AJAX update if any of them change. So with no placeholders in the WHERE clause, we aren't setting up any triggers.

And ... not sure where the "Filter WHERE" comes in ... that's ONLY used in the element filters on a list, if you have a specific clause you only want to use when filtering the list on that element. And AJAX updating doesn't happen for filters on lists, only for the join element on the form.

I guess we;re misunderstanding what exactly you are trying to do. Are you talking about filtering a list? And if so, what feature do you need that you don't get with just enabling list filtering on the join element?

-- hugh

 

[pie]

Allright, let's start from scratch:
I have a database join element that I use to schedule events in places:
every place has a dbjoin element linked to the events list to confirm its will to host it. (many events may be hosted in different places at the same time).
I am actually using the checkbox display mode, but events are many and of various kind, so to avoid confusion I'd like the user who has to select them - the place Manager - to be able to filter events by type, or at least to group them.

I'm not sure about what you mean when you say enabling list filtering on the join element:
I didn't see any other option with the filter word in the join element that seems to apply to form view.

About the AJAX filter, I based my test on the hint text, maybe I misunderstood it: I switched display mode from checkbox to multi select dropdown to make use of the AJAX filter (I'd like to see it running to understand what it does at this point.. )
I tried using a placeholder in the first WHERE but I get an sql error 1064; I am sure I am messing things up: may I ask you how would you write it knowing that:
in the first WHERE there already is

{thistable}.start >= Curdate()

because I need to show only upcoming events
and I need to filter the dropdown content upon {thistable}.type (aka {events___type} ) field?


Thank you a lot

 

[cheesegrits]

OK, so you are doing this on a form.

Then definitely ignore the "Filter WHERE", as that's only applied to list filtering.

Here's an example of AJAX filtering a multiselect. I'm joining to my us_states table, and filtering the state name on a field element, with ...

{thistable}.state LIKE '%{fab_checkbox_join___name}%'

https://www.screencast.com/t/uxGt5wjw

As you can see, when I change the 'name' field, the AJAX kicks off and updates the checkbox selection accordingly.

It seems that this feature does work with multiselects.

-- hugh

 

[Sophist]

I have this working on my site (with a dropdown).

I wrote a {thistable}.filter = '3' inside the data-where Filter where textarea and enabled AJAX update.

The ajax is triggered based on watching the placeholder elements that you use in the WHERE clause. In this case you have not used any placeholders, so there are no elements that are watched and ajax will not be triggered. So you would need something like {thistable}.type = '{place_type}' and then the dbJ would watch the place_type field in the form and when it changed would run ajax to repopulate the dropdown.

looking at the query with fabrikdebug I can only see the first part of the where query (no Filter where) and the output shows every entry.

fabrikdebug only gives you details of what happens for the main page display - ajax calls are not included in fabrikdebug.

 

[pie]

Thank you both, I got it working with the multiple selection dropdown
I am wondering if it's possible to mod the multiselection dropdown to work as if the CTRL key is always pressed: not everyone is confortable using the combo keyboard+mouse.

One more question: is it expected that if the list is filtered by AJAX, the current selections are lost if I refilter the list?
Is there something we can do to avoid this?

steps to reproduce:

joined table contains:
event, type of event

Select 2 entries and save.
Goi back to the form to update the schedule you should correctly see the already selected entries n the dropdown list.
If you filter the dropdown now, the selection clears out, and if you save the form you lose the already made selections.

thank you

 

[cheesegrits]

I can't replicate this.

Has the element you are filtering on changed somehow?

-- hugh

 

[pie]

No js in its properties, if that's what you mean.
Since I have to read an id number in the event table (event_type) I am using a dropdown dbjoin element as filter element, if this may make a difference.
However I tried changing it to field and the issue is the same:
everytime I filter it, it "forgets" the selection (current and previous)

thank you

 

[cheesegrits]

What site / form is it?

I still can't replicate the problem, with either a text or a join as the 'watched' element.

-- hugh

 

[pie]

I am sending you a PM with the details if you can take a live look at it,
thank you

 

[pie]

update: I had to revert to a previous backup because I made a mess...
Don't know if this may make a difference, but I am still on J! 3.8.4 (this backup was before updating)
Right now the filter is working for the first time, but as soon as I filter the list again it still "forgets the selection"

thank you

 

[cheesegrits]

Can you put the credentials in your My Sites, http://fabrikar.com/you/my-sites, rather than PM'ing me. I delete my PM's on a daily basis.

-- hugh

 

[pie]

I already did it: you should have both for front and backend, and a note explaining where to try the element

 

[pie]

friendly bump

 

[cheesegrits]

You had set "Store in DB" on the Torneo element to "No". So yeah, it'd lose it's value. That's kinda what happens when you don't store the value in the database.

I also modified the access controls, thinking that might be the issue, and I forgot what they were set to originally, so you'll need to set them back.

-- hugh

 

[pie]

Hi Hugh, sorry for the delay: I made some tests but it is still not working for me.
Storing Torneo in db just saves the value of the element, but it's not what I need to save: this element is meant to be only a filter for the dropdown element.
The issue is that it does save the multiple selection, but "only the first time" - meaning that it saves everytime, but everytime it is like is the first one.

It doesn't reload it (or if does, it doesn't show as "selected" the already made selection) if you edit the field again and the Torneo element is saved in the DB.
The same thing happen if you:
1)filter the dropdown by value 1,
2)select one entry,
3)filter the dropdown by value 2,
4)filter the dropdown by value 1,
at this point the selection made at step 2 is "not selected"

Maybe I should save after I make my selection (step 2 of the previous list) but if I then want to select some more entries, I miss the previously selected entries.

Am I doing something wrong? Is it not supposed to work like that?
Thanks