Solved where filter in databasejoin is ignored

[EricWebsite]

Hi,

In Fabrik 4.0 official, an attempt to use the 'Data - where' filter options ('Joins where ... ' or 'Filter Where') resulted in an error "error in sql statement ..." while there was nothing wrong with it.

After updating from git, version from May 3rd, anything you put in the filters is being ignored. Even 'where 1 = 0' has no effect.

My databasejoin element is set to Checkbox, table Employee linked on id to table Location. The query is
"WHERE '{employee___show_email_and_location}' = 1 ORDER BY location.display_ordering"
Replacing '{employee___show_email_and_location}' by {thistable}.show_email_and_location etc makes no difference.

All the other settings (like Apply where to etc) are default but seem to make no difference either.
I am on Joomla 4.4.4.

 

[troester]

Checkbox with WHERE is working fine here.

I don't get your setup.
Which is the table you are connecting to in the dbjoin? location?
What do you want to achieve?

 

[EricWebsite]

Yes, in the employee table I have a databasejoin to the location table. The name of this dbjoin is also 'location'. That works fine; selected locations are in the list and form.

Some employees want to hide their location (and email) so there is also a yes/no element in the employee table called 'show_email_and_location'.
What I want to do is: only show the dbjoin content when 'show_email_and_location' is yes (1).

But whatever I put in the Where filters, locations are always shown.
Even if the where clause evaluates to false, or is defenitely wrong and should throw a syntax error.
A sort in this field does not work anymore after the git update, it did on 4.0 original.

Hope that makes sense!

 

[troester]

This is not the way it's working.

Data-where will determine which options are shown in form view (and how they are ordered). Both is working.
So with WHERE 0=1 you won't see any option in form view.

List and details view are showing what is stored in the DB (in case of multiselect elements stored in xy_repeat_element-name tables).

If you want not only to hide an element (via CSS or JS or a custom template, all these could be overcome by knowing users) you must use some onLoad plugins to remove the data if some conditions are met.

 

[EricWebsite]

I see!
I'm sorry, I did not know that this only works on Form view!
Indeed I could have done want I wanted with CSS or JS, but that does not remove it from the output so it can stil be viewed.
Thanks for your suggestion to use the list filtered by the php_events plugin, onLoadData, works perfectly.

 

[EricWebsite]

For others what I came up with in the php_events list plugin, onLoadData was:

$data = $model->getData();
foreach ($data as $group) {
  foreach ($group as $row) {
    $show = $row->employee___show_email_and_location;
    if ($show == 0) {
        $row->employee___e_mail_address = '';
        $row->employee___location = '';
    }
  }
}

That does stop the fields e_mail_address and employee___location to be shown in the list, but they still show up in Details view.
How to do that?

 

[troester]

A form php plugin running onLoad, doing the stuff only if it's details view.