prefilter query

[von Horst]

when I run this query in phpmyadmin, it works as expected:

SELECT scope, historical FROM serverlist05 WHERE (scope = '0' OR scope is NULL) AND (historical = '0' Or historical is NULL)

but when I use it as prefilter query on fabrik list, it does not filter anything.
no other prefilters are active, not in list, not in menu item.

what am I missing?

 

[troester]

How/where did you put this query?
In the prefilter you have something like
WHERE selected-element EQUALS-or-some-other-condition some-value

so you'll need eg (assuming serverlist05 is the list you are setting the prefilter)
WHERE id IN
value:
SELECT id FROM serverlist05 WHERE (scope = '0' OR scope is NULL) AND (historical = '0' Or historical is NULL)
type:
query
apply:
public

 

[von Horst]

great, thanks a lot, now I understand how prefilter works

 

[troester]

An other way would be to add multiple prefilters and combine them via OR/AND and the "grouped" option

WHERE scope EQUALS 0
OR scope IS NULL (grouped=yes - I think)
AND historical EQUALS 0
OR historical IS NULL (grouped=yes - I think)

but I'm always unsure how the "grouped" will group (have to enable fabrikdebug and check the generated query)

So with complex AND/ORs I think the "id IN (SELECT id FROM...)" is the easier way (hoping the DB itself will optimize the "double" query)

 

[von Horst]

yes I tried the prefilter combination before, but it never worked as expected for me. AND does not seem to be dominant over OR.
"id IN" solution works great

 

[cheesegrits]

The AND/OR does work, although as troester said, you may need to look at the generated query (getData in fabrikdebug mode) to see exactly what is going on in the WHERE clause.

-- hugh