start/finish prefilter

[prophoto]

Can you tell me what I'm doing wrong here? This query works perfectly...
SELECT * FROM `fab_classes` WHERE status=1 AND `startpub` <=now() AND (`finishpub`>= NOW() OR `finishpub` LIKE '0000%')



But for the life of me I can't replicate it in the list prefilter. I need to query based on 3 fields (just like Joomla articles), status is Published = 1, Start publishing and finish publishing. Just like in Joomla articles finish publishing = '0000-00-00 00:00:00' when not filled in and I have 'default to today's date' turned off in the finish publishing element. Start publishing has 'default to today's date' turned on.

The fabrik list prefilter returns different results vs the sql query that works perfectly.

 

[Sophist]

1. Start Publishing should probably be LESS THAN OR EQUALS to NOW() not GREATER THAN EQUALS. Similarly Finish Publishing should be GREATER THAN OR EQUALS not LESS THAN.

2. You need to check what exactly the SQL is that is being created. I suspect it might be doing "(Status = 1 AND Start_Pub <= NOW() AND Finish_Pub = 0 ) OR Finish_Pub >= NOW()" rather than "(Status = 1 AND Start_Pub <= NOW() ) AND ( Finish_Pub = 0 OR Finish_Pub >= NOW() )". (We really need to think about how to allow users to add brackets to the filters.) If this is the case, try doing it like "Finish_Pub = 0 OR Finish_Pub >= NOW() AND Status = 1 AND Start_Pub <= NOW()" instead.

3. You should try not to use CONTAINS if you can help it because it will most likely not use indexes. Either check for Finish_Pub = "0000-00-00 00:00:00" or (according to my Google search) Finish_Pub IS NULL which will do the same thing.

Hope this helps. Let me know how you get on.

 

[prophoto]

finishpub ="0000-00-00 00:00:00" produces a 500 error. How do I check the query again?

finishpub ISNULL gives me no records.



Here is the current dataset

 

[prophoto]

finishpub=0 appears to be working, thanks!

 

[cheesegrits]

(We really need to think about how to allow users to add brackets to the filters.)

That's what "grouped" does (means "group with parens to the previous filter"). So his setup will produce ...

WHERE filter1 AND filter2 AND (filter3 OR filter4)

-- hugh

 

[Sophist]

Well - more fool me - I assumed that Grouped meant something to do with SQL GROUP BY. D'oh!

 

[cheesegrits]

Yeah, not the best choice of word, but it's been called that since the dawn of the Fabrik universe.

-- hugh

 

[Sophist]

I will try to create a PR to add tooltips to the column headings.