Pre-filter queries: addition of an extra OR statement might be behaving as an AND

[chozma]

Hi guys,

I'm trying to add some pre-filters to a list I have. The list contains data about animals and is joined with three other tables. I've got lots of pre-filters going on that all work brilliantly but I need to add an additional pre-filter to one of the groupings I have.

At the moment the grouping I want to expand on selects records that:
* have a field called 'current' which equals 1 for each record with a specific id
or
* has no mention of a specific id

Now if either of those conditions fail I also want it to select:
* the max date from a field called 'joined' for all records that have a specific id and have 'current' set to 0.

So I've added the last pre-filter as an OR and grouped it as per the attached image.

So you can see more specifically the code I have used in the prefilters above, here are the queries:

app_mob_animal_Mob
 
EQUALS
 
SELECT Mob
 
FROM app_mob_animal
 
WHERE app_animal.id = app_mob_animal.animal AND app_mob_animal.current = '1'

OR

app_mob_animal_Animal IS NULL

OR

app_mob_animal_Joined
 
EQUALS
 
SELECT MAX(Joined)
 
FROM app_mob_animal
 
WHERE app_animal.id = app_mob_animal.animal AND app_mob_animal.current = '0'

Its not doing what I would expect as it seems to be treating the last statement as though it was an AND.

I can't figure out how to extract the SQL to see what's going on.

I've tried using debug mode but all it lets me see is the data structure for joins and no SQL.


What do you think might be going on here? Some advice would be great as I'm all out of new ideas on how to approach this one!

Kind regards,

Hannah
x

 

[troester]

The debug is for debugging the list view not the list settings.
So display your list (front- or backend) with &fabrikdebug=1 added to the URL.
You should see "list GetData: your-list-name" which contains the generated query.

Grouping is tricky, maybe you have to change the order of your prefilters to get the grouping as needed.

An other possibility is to use
WHERE id IN
value: SELECT id FROM your-table WHERE ...here do the ANDs and ORs with correct brackets yourself...

 

[chozma]

Thanks Troester!

I only tried viewing the debugging in the frontend list and I got 404 error. But now I have tried to view the debugging on list in the backend and I can see the SQL now which is great. Thanks for that suggestion.

It does seem that the SQL is outputting the last statement as an OR so that's good news. But as you say grouping is tricky so its bad news that I haven't got it right yet!

I'll try the WHERE id In and see how I get on with that. Fingers crossed!

 

[chozma]

Oh yes, I'll give reordering them a go as well. I can't see a way of dragging and dropping the pre-filters into a new order - do you know of a nifty way to do that at all?

 

[cheesegrits]

I only tried viewing the debugging in the frontend list and I got 404 error.

I suspect you are using SEF, so the fabrikdebug=1 would be your first query string argument. Which means it needs to be ?fabrikdebug=1. The query string arguments themselves are & seperated, but the the query string part has to be seperated from the URL part with a ?.

-- hugh

 

[cheesegrits]

In other words if you were not using SEF, it would look like ...

http://your.site/index.php?option=com_fabrik&view=list&listid=123&fabrikdebug=1

... with the ? after the URL part, and before option=. But with SEF, it be something like ...

http://your.site/fabrik/list/123?fabrikdebug=1

... ot whatever SEF format you are using.

-- hugh

 

[cheesegrits]

Oh yes, I'll give reordering them a go as well. I can't see a way of dragging and dropping the pre-filters into a new order - do you know of a nifty way to do that at all?

No, unfortunately there isn't a drag and drop re-order. You have to delete / rebuild.

-- hugh

 

[chozma]

Thanks Hugh, I'm still playing with the prefilters at the moment to see if I can get something behaving as I need.

And yes you are dead right about using SEF, that explains why I couldn't see the debugging stuff.

Hannah