Filters not properly showing "all" or "recorded data" with multiple/repeat DB joins

[lousyfool]

Hi, having an issue again... sorry, also for the lengthy post...
Studied Wiki and, thinking I can't be the only one, I've also tried searching the forum, but can't find a solution.
Turned on debugging to see if I can find where a query might go "wrong", but not knowing where exactly to look, it ended up being TMI...

In use: J! 3.9, F 3.9 (Github from about a week ago), PHP 7.2.

For my lists, filters above them are supposed to show only list/table/recorded data only, and settings globally as well as for list and elements are accordingly.
This works perfectly fine for single selects incl 'databasejoin', but not for multiselect 'databasejoin' elements where the data is stored in a '_repeat_' table.

For example, I'm having a multiselect 'Brands' DB join element, where the joined table has appr 500 rows (global 'Filter List Max' set to 1000), but only a fraction is recorded in the (repeat) table. Of course, I want to show only recorded data in the filter, but no matter what I choose for filter settings for the element
- Filter type: (single) dropdown, or checkbox, or multi-select list
- Filter data: global, or recorded, or all
it always gives me the very same odd filter options. It seems as if the filter has a mind of its own since it doesn't react to those changes of settings; even 'all' won't show all.

The list is a copy of another one ("original") but with specific pre-filters in the copy list while the original has no pre-filter. Eventually menu items for the copy list each have another pre-filter added. Of course, copy list results are correctly different from the original, but the options in the filter for the multi 'databasejoin' do not vary at all from list to list, they're always the same, also in original and copy, although they shouldn't be. Haven't checked every option but it seems the filter options are as data recorded in the original list - also in copy list, regardless of pre-filters there.
And again, setting everything explicitly to "show all" for filters doesn't change anything anywhere, either.

By the way, ordering the filter options by 'label' or 'value' also both doesn't give expected results, respectively... the order is different for either setting but not alphabetically when set to label nor by ID when set to value.

And all of this is true both in the frontend and backend.

Am I doing something wrong? Or is this a limitation of Fabrik?

I'm attaching screenshots, just in case.

Hope I described it ok. Thanks for a heads-up!

 

[cheesegrits]

but the options in the filter for the multi 'databasejoin' do not vary at all from list to list, they're always the same, also in original and copy, although they shouldn't be. Haven't checked every option but it seems the filter options are as data recorded in the original list - also in copy list, regardless of pre-filters there.

Join element filtering is independent of list filtering. You are joining to a table, not a Fabrik List (check the dropdown where you select the table the join is to ... it's not a dropdown of Fabrik lists, just raw tables). So we don't apply prefilters from any lists the table you join to may (or may not) be a part of, when building the join options. The only way to filter those is to use the "where" clause in the join settings.

I'll take a look at the main issue, which iiuc is multi select joins not honoring "recorded data" in filters?

BTW, with &fabrikdebug=1, the title for query that builds the filter options list for a join is either "element filterValueList_Exact:" (for "Recorded Data") or "element filterValueList_All:" (for "All"). That query should show you what's going on.

-- hugh

 

[lousyfool]

Thanks Hugh,

For looking into this.

The only way to filter those is to use the "where" clause in the join settings.

I'll take a look at the main issue, which iiuc is multi select joins not honoring "recorded data" in filters?

Correct, that is the main issue.
I understand that, for being able to use the "Filter Where" clause, the filter must be set to "Show all". And there's my problem again: whatever that setting is, "all" or "recorded only", the filter options are never what you'd expect for multi-select joins.

Presuming this to work again, and while at it, but if you had the briefest hint on what to put into the "Filter Where" field, I'd hugh-ly appreciate it. Seems my brain is spaghettis right now...

 

[troester]

BTW, with &fabrikdebug=1, the title for query that builds the filter options list for a join is either "element filterValueList_Exact:" (for "Recorded Data") or "element filterValueList_All:" (for "All"). That query should show you what's going on.

So what do you get with fabrikdebug?

 

[lousyfool]

So what do you get with fabrikdebug?

Nothing at all regarding filters for the multi-select join like in my "brands" example!

First, I had two sections "element filterValueList_Exact" with queries, but not containing "brands" (= the multi-select join).
So, I've turned off all other filters except for the one for the multi-select join, just to get rid of irrelevant stuff in debug. Then there's no more section "element filterValueList_..." in debug anymore at all (though the "brands" filter is still in the filter container, of course).

The only mentions of "brands" are in list/table data and under three "joins" sections, but guess that's normal and not helpful for the issue on hand...

Not sure if this all helps, but thanks for hanging in here with me anyway.

Now what?

EDIT:
@troester - guess the screenshot slipped accidentally into your post? Lol, never mind!

 

[cheesegrits]

Ah, looking at the code, it seems like you are correct, we don't differentiate between "all" and "recorded" for multi-select / checkbox joins.

We should however show "recorded" (for both) correctly.

I've added a new debug output ("join checkboxRows") in github, which will show the query being run.

I had a look at making checkbox joins honor the "all", but it's not trivial (which is probably why we didn't do it first time round!).

-- hugh

 

[lousyfool]

Yeehaw... now there's something!

SELECT `j_bc_content_repeat_brands`.id AS id,`j_bc_content_repeat_brands`.parent_id, `j_bc_content_repeat_brands`.`brands` AS `value`, `j_bc_brands`.`brand` AS `text`
FROM `j_bc_content_repeat_brands`
LEFT JOIN j_bc_brands ON `j_bc_brands`.`id` = `j_bc_content_repeat_brands`.`brands`
WHERE  `j_bc_brands`.`published` = 1
ORDER BY `j_bc_content_repeat_brands`.`brands` ASC  LIMIT 1000

 

[lousyfool]

Hi Hugh,

Ok, I had about 3 minutes when posting the previous one. Since then I've had more opportunity to take a look or two.

Ah, looking at the code, it seems like you are correct, we don't differentiate between "all" and "recorded" for multi-select / checkbox joins.

We should however show "recorded" (for both) correctly.

I dare guessing that for most use cases - incl mine - it'll be fine to always show "recorded" only. But, unfortunately, that doesn't work at all. Instead, it is always "all".

Obviously, the query as per debug "join checkboxRows:" (see my previous post) doesn't do for the purpose as it doesn't join to the list table (you know what I mean). It does produce "all".

For the jux of it, I added another pre-filter to the menu item, so only a single list item would be displayed which happened to have two brands assigned to it.
As always, the brand filter still showed 51 brands. Well, that's all distinct brands in the `j_bc_content_repeat_brands` table, as verified in phpMyAdmin with

SELECT DISTINCT `j_bc_brands`.`id`, `j_bc_brands`.`brand` FROM `j_bc_brands` JOIN `j_bc_content_repeat_brands` ON `j_bc_brands`.`id` = `j_bc_content_repeat_brands`.`brands`

Again, the same as if "show all" was set in Fabrik.
Oh, and ordering is also identical in both filter and SQL: by ID, not label.

Also obviously (and unfortunately), the "Filter Where" field is neither practically useful nor helpful at all for tweaking it. I can't even change the filter ordering with an 1 = 1 ORDER BY whatever.

I fear this requires you tweaking the code... sorry, sorry. Really looking forward to having good news from you soon.
Thank you once again in advance.

 

[cheesegrits]

I think we may have a terminology misunderstanding.

But, unfortunately, that doesn't work at all. Instead, it is always "all".

No, it's always showing "recorded". Which means it shows only those which have been selected in submitted data ("recorded" with the form). With 'repeat' join types (checkbox and multi-select) we create that "junction" table (j_bc_content_repeat_brands), a many-to-many join table, which maps the selections on each submitted form (parent_id) to the join's target table. The checkboxRows() function always selects from the junction table, and left joins the target table.

Showing "all" (every record from the join's target table, regardless of whether it has been selected in submitted form data) would mean ignoring the junction table, and simply selecting everything from the target table.

I did spend about an hour fiddling around with this yesterday, and it's not trivial. Which is probably why we just fudged round the issue in the first place. I don't know when / if I'll have the time to implement the 'all' method for repeat joins, on spec. It's one of those things that will probably happen when a paying client needs the feature. I'll have another go at it today or over the weekend, but I'm not promising anything.

-- hugh

 

[lousyfool]

Thanks for your reply, Hugh.

So, I understand the term "recorded" must be interpreted as "anything recorded = present in the DB table".
Hmm, fair enough. But then we need to talk about how much or little sense this makes.
Seems you can see this: You create a list, then you copy it eventually multiple times to apply different pre-filters. You use multiple menu items or modules with their own settings/pre-filters to access the same list for different results and purposes. All fine, except the multi-select join filters do give you options having little to do with those pre-filters... "recorded" data may be a technically correct term, yet it's little to not "relevant". It might have to do with the list in the tables, but not with the list displayed.
So it goes with multi-select joins. While single-select joins seem to work just fine... "recorded" AND "relevant". Hmmmmmmm.

I know you're getting the point, you've indicated it, kinda... thanks.
Now, seriously, what would it take to get it right? No doubt it's not trivial, but what does this translate to?

Filters are good, sometimes life-savers. But in system with large and even increasing data volume I can't give people a filter offering dozens if not more options if some (pre-filtered) lists have only a possibly tiny fraction of those as values across their rows... that makes them useless and, worse, a cause of huge frustration.

Thanks for not giving up on this... on me...

 

[cheesegrits]

I ... don't understand any of that. How does 'recorded' vs 'all' in filters relate to pre-filters and menu items?

If I fix what is currently wrong, the only difference will be that when you select "All", you'll get every row in the joined table showing in the filter.

For example, my test case on this is a "US States" checkbox. So there's 53 rows. In my submitted data in the form, I've got a total of 7 states have been selected in the recorded data. So my checkbox "US States" filter shows 7 states to choose from. If I fix the 'all' issue, the filter will show 53 checkboxes instead of 7.

I don't see how that relates to your concerns?

-- hugh

 

[lousyfool]

If I fix what is currently wrong, the only difference will be that when you select "All", you'll get every row in the joined table showing in the filter.

Hi Hugh,

Sorry, but it seems there's a misunderstanding.
Now, instead of more words back and forth here, let me please show you. I've built a test site with fresh Fabrik 3.9 incl Github "out of the box". A list with a few records, and your US States example in 4 elements. Two elements are DBjoins (one "multi", one is "single"), and two are regular (one checkbox, one radio).
Then three list views, one with all the records, and two based on list copies and pre-filtered (one in the list, one in the menu item).

Just check and compare filters in the various lists, and you'll see:
- In all cases, only the multi DBjoin filter's ordering is incorrect.
- In the (pre-filtered) list copies, the multi DBjoin filter keeps showing "too many" options, while all other filters incl the one for checkbox show what one would expect (= what's "recorded" for the list as displayed).

Actually, if you consider the multi DBjoin filter to act "correctly", then the checkbox filter must be wrong!

See at https://fabrik.lousyfool.com - the page there explains it also.

If you want backend/FTP access to double-check or so, no prob... I just can't find where to enter such access credentials here.

Thanks again, and hope it's clear now.

 

[lousyfool]

Quick update:

Was running out of time again when going through above exercise...
Since then, I've removed all my refs to "Search all" filter as they were completely irrelevant... (what was I thinking?!).

On the test site, I highlighted the good and bad filter options. Should be very clear now!
Also, debug is set to yes. From what I see there, the query for the checkbox filter is more like what we need and want for the multi DBjoin filter as well when set to "list/table/recorded data".

 

[cheesegrits]

OK, now I understand. Amongst other thing, even in "show recorded" mode, it's not applying list pre-filters.

Thanks for the clear and concise test site.

I'll see what I can do. I've looked at it some more, and again I can see why we kinda skirted round that first time round and just did the simple minded thing. I'll give it a go, but I can't guarrantee when / if I'll have a fix.

-- hugh

 

[lousyfool]

Fantastic - thanks a ton, Hugh!

Don't think anyone expects a fix over night. Better well than rush-rush.
In the meantime I might be unable to stay away from taking a curious look myself, hehehe.

Thanks for an excellent product and support at an unbeatable price!!!
(BTW, when will subscriptions be available again? I'm due!)

 

[cheesegrits]

We're doing away with subscriptions. They haven't come close to paying for themselves for about 6 years, barely breaking 4 figures on a good month. I get it ... people naturally only sign up for a month when they need it, like when updating to a new version, or when implementing some new features ... but that doesn't help cover all the development time that goes on behind the scenes. And a month's sub only covered a scant handful of minutes of my time (a standard sub covered about 13 minutes of my time), which often got eaten up by the first question in a month, and by the end of a month I'd be many hours in the red for most subscribers.

This issue is a classic example. It'll take me a day or so of work. And although you might think "well, it benefits all Fabrik users" ... there's only a tiny handful of people who use checkbox joins with filters, who care about all vs recorded with pre-filtering ... and you are the only person ever to raise it as an issue. So yeah, although it'll benefit maybe 3 people over the next few years ... I'm really doing it for you. It's not even a feature I need for any of my direct paying clients atm.

So, we're moving to a "pay as you go" regime. If people need work done, they pay an hourly rate. I'll fix features that I need for our direct clients, who are the folk that actually pay the bills and keep Fabrik going. Anything else, it's hourly rate work.

We tried for 10 years to provide "all you can eat" subscription support, but there just aren't enough Fabrik users who place enough value on it to maintain subscriptions.

-- hugh

 

[lousyfool]

<off-topic>
Uh, that sounds awfully frustrated. Sorry.
And not surprised, though. For their cars' oil change people pay proper hourly rates to an expert. But "anything Internet", apps, even support and custom work must be free, worst case dirt cheap. Like prolly the majority of this industry, I experience this all the time as well, grrrr.

Anyway, bet you have your reasons why you're not going for one of the usual subs model, though I (and still many others) have no qualms paying an annual fee for initial license plus updates and the occasional support question. Especially if it's good. And this is very good.

So, how do I buy you at least a laaarge coffee?
</off-topic>