Problems combining AND and OR in GET filter

[haydonwebdesign]

I've read quite a few threads on this topic, as well as the Fabrik tutorial which touches on the issue, unfortunately I've been unable to find the solution so far.

Our current URL looks like:
?resetfilters=1&industry[value][]=0014&industry[join]=OR&industry[value][]=1001&location[value][]=1004

Which filters the results as follows:
(industry = 1001 OR industry = 0014 OR location = 1004)

However, in this case I'm trying to achieve this:
(industry = 1001 OR industry= 0014) AND (location = 1004)

And in the general case I need to achieve the following:
(industry = 1 OR ... OR industry =x) AND (location = 1 OR ... OR location = x) AND (company = 1 OR ... OR company = x) AND (level = 1 OR ... OR level = x)

To describe this in words:

• four fields to filter by
• between filter values for a single field => connected by OR
• between filters on different fields => connected by AND

I can happily provide the actual URL to the page, but would prefer not to post it here publicly.

 

[troester]

See

• Grouping multiple sub queries

Not sure if you can get any possible logic this way.

 

[haydonwebdesign]

Thanks for your response. I already read that page through and wasn't able to reproduce either the results described on that page, or the results I'm trying to achieve. In fact I had the feeling that the examples listed there have errors in them - certainly the order of parameters appears inconsistent.

Would it be possible to either get a single working example to start me off?
Or else confirmation that what I'm trying to do isn't fundamentally possible?

 

[cheesegrits]

Yeah, it's not easy. I can never write these things off the top of my head, I have to sit down and play around. Uing &fabrikdebug=1 (and making sure debug is enabled in the Fabrik global options) helps, as you can then see the exact WHERE clause being generated in the getData() query.


?fabrikdebug=1&resetfilters=1
&uk_cities___postcode[value][]=AB12
&uk_cities___postcode[value][]=AB10
&uk_cities___postcode[join]=OR
&uk_cities___postcode[grouped_to_previous][]=0
&uk_cities___postcode[grouped_to_previous][]=1
&uk_cities___id_raw[value][]=1
&uk_cities___id_raw[value][]=2
&uk_cities___id_raw[join][]=AND
&uk_cities___id_raw[join][]=OR
&uk_cities___id_raw[grouped_to_previous][]=0
&uk_cities___id_raw[grouped_to_previous][]=1

... produces ...

WHERE ( ( `uk_cities`.`postcode` = 'AB12' OR `uk_cities`.`postcode` = 'AB10' ) AND ( `uk_cities`.`id` = '1' OR `uk_cities`.`id` = '2' ) )

-- hugh

 

[cheesegrits]

BTW, when you get yours working, feel free to update the wiki with a working example.

-- hugh

 

[haydonwebdesign]

Hi there,

Thanks so much for taking the time for this.
Based on your example we extended the URL to the following:

pageurl?fabrikdebug=1&resetfilters=1&
field_1[value][]=0001&
field_1[value][]=0002&
field_1v[join]=OR&
field_1[grouped_to_previous][]=0&
field_1[grouped_to_previous][]=1&
field_2[value][]=0003&
field_2[value][]=0004&
field_2[join][]=AND&
field_2[join][]=OR&
field_2[grouped_to_previous][]=0&
field_2[grouped_to_previous][]=1&
field_3[value][]=0005&
field_3[value][]=0006&
field_3[join][]=AND&
field_3[join][]=OR&
field_3[grouped_to_previous][]=0&
field_3[grouped_to_previous][]=1&
field_4[value][]=0007&
field_4[value][]=0008&
field_4[join][]=AND&
field_4[join][]=OR&
field_4[grouped_to_previous][]=0&
field_4[grouped_to_previous][]=1

This correctly generates the following WHERE clause:

(field1 = 0001 OR field1 = 0002) AND (field2 = 0003 OR field2= 0004) AND (field3 = 0005 OR field3 = 0006) AND (field4 = 0007 OR field4 = 0008)

 

[cheesegrits]

It'd be great if you could add that to the wiki.

-- hugh

 

[haydonwebdesign]

We're still testing this solution, but I will endeavour to add it to the wiki when it's finally completed. The following URL:

/URL?resetfilters=1&fabrikdebug=1&

field_1[value][]=0001&
field_1[join][]=AND&
field_1[grouped_to_previous][]=0&
field_1[value][]=0014&
field_1[join][]=OR&
field_1[grouped_to_previous][]=1&

field_2[value][]=0000000110&
field_2[join][]=AND&
field_2[grouped_to_previous][]=0&
field_2[value][]=1000000004&
field_2[join][]=OR&
field_2[grouped_to_previous][]=1&

field_3[value][]=0003&
field_3[join][]=AND&
field_3[grouped_to_previous][]=0&
field_3[value][]=0004&
field_3[join][]=OR&
field_3[grouped_to_previous][]=1&

field_4[value][]=00000002&
field_4[join][]=AND&
field_4[grouped_to_previous][]=0&
field_4[value][]=00000003&
field_4[join][]=OR&
field_4[grouped_to_previous][]=1​

Generates what for us is the correct form of filter when working with multiple multiselect dropdowns:

(field_1 = ... OR field_1 = ...) AND
(field_2 = ... OR field_2 = ...) AND
(field_3 = ... OR field_3 = ...) AND
(field_4 = ... OR field_4 = ...)​

 

[haydonwebdesign]

We have now almost cracked this, but in the final stage of implementation have come up against what looks like a bug in Fabrik. Our main search page features a search box (extended search, match all) along with 4 multi-select filters (industry, location, entry level and company). I've written a PHP script to automatically generate/print the corresponding URL (with GET parameters) to match the current filter/search status (POST). The URLs generated correspond to the structure mentioned above, and we've used fabrikdebug to confirm that they do exactly what they should be doing.

That should be the end of it, but ...

... when the corresponding URL is opened in the browser, thereby triggering a filter/search through GET parameters:

• The data is filtered correctly
• 2 of the 4 multi-select filters display correctly
• The other 2 only show the last filtered value

Here's an example:

• Actual filter (URL and displayed data)
  • Industry: 1 OR 2 OR 3
  • Location: 4 OR 5 OR 6
  • Entry level: 7 OR 8 OR 9
  • Company: 10 OR 11 OR 12
• Displayed filter (status of multi-select fields)
  
  • Industry: 1 OR 2 OR 3
  • Location: 6
  • Entry level: 7 OR 8 OR 9
  • Company: 12

I've looked into every possible explanation/cause I can think of, but have made no progress in identifying the issue. I'll be happy to send a PM with URLs to both pages and you can happily take a look directly. I would also be happy to pay for the support/dev time to solve this issue, since ... well, frankly I'm completely stumped.

 

[haydonwebdesign]

Friendly bump
I've reached a dead end here .. and so close to the final goal!

 

[haydonwebdesign]

One month on ... any chance you take a look at this still?
We're under pressure to find a fix and since this seems to be a bug, we're faced with the difficult choice between solving this one issue vs. migrating completely to a different system. As I wrote, we would also be happy to cover the costs for this support/solution ... would be hugely grateful for your help!

 

[cheesegrits]

Hey, sorry, I was out of town on the road for a couple of weeks (graduation and other events for my kids) this month, this fell through the cracks.

I think the only way I could work on this would be by installing an Akeeba clone of your site here, and setting up a PHP Storm project on it, so I can actually properly debug it. That adds about an hour overhead before I even start working on tracking and fixing the solution. And I can't really give an estimate of how long actually tracking and fixing will take. Could be 10 minutes, could be a day. Filtering is by far and away the single most complex part of Fabrik, as it has so many moving parts, juggling pre-filters, posted filters, query string filters, session filters, advanced search filters, form filters, etc., and keeping all those compartmentalized between lists.

So all I can really do is offer to work on it on an hourly rate, and you give me the max number of hours you want me to expend.

-- hugh

 

[haydonwebdesign]

Sounds good to me, thanks for the offer. Have sent you a PM.

 

[haydonwebdesign]

Hi there Hugh, I've sent you a couple of PMs now with no response?

 

[cheesegrits]

Sorry, I've been busy, and pm's tend to get buried. I'll take look now.

Sent from my HTC6545LVW using Tapatalk