Problem with date fields (using pre-filters)

[machelp]

We are using Fabrik as a content-replacement. We have multiple forms that we use to generate content to show (like news and events). The forms we made needs to have publishing options.
So, we made 3 elements, a yes-no element and 2 date elements. The date elements are giving problems, we use the pre-filters to check if the publish date is lower then now, and the unpublish date later (greater) then now. If there is a submission without those filled it, it won't even show, so we added a grouped OR statement for both, with the check if it's 0000-00-00 00:00:00.
This works if i just query it via phpMyAdmin (handwritten copy), but doesn't seem to work in the component.

Am i doing something wrong, or is this a 'bug' in Fabrik?

We're using Fabrik 3.1 with Joomla 3.3.3

 

[troester]

With Joomla3.3+ you need Fabrik3.2 (and latest Joomla is 3.3.6), but this shouldn't be the issue here.

What are your pre-filter settings (screenshot)?
You can enable Fabrik debug (in Fabrik options) and then append &fabrikdebug=1 (or ?fabrikdebug=1 if there's no other URL parameter) to your URL.
A click on getList Data will show the generated query.

 

[machelp]

I don't see a getList Data option in the debug, but i do see a list getData option.
That gives me an database query:

Spoiler

SELECT DISTINCT `nieuws`.`id` AS `nieuws___id`, `nieuws`.`id` AS `nieuws___id_raw`, `nieuws`.`date_time` AS `nieuws___date_time`, `nieuws`.`date_time` AS `nieuws___date_time_raw`, `nieuws`.`titel` AS `nieuws___titel`, `nieuws`.`titel` AS `nieuws___titel_raw`, `nieuws`.`subtitel` AS `nieuws___subtitel`, `nieuws`.`subtitel` AS `nieuws___subtitel_raw`, `nieuws`.`introtekst` AS `nieuws___introtekst`, `nieuws`.`introtekst` AS `nieuws___introtekst_raw`, `nieuws`.`vervolgtekst` AS `nieuws___vervolgtekst`, `nieuws`.`vervolgtekst` AS `nieuws___vervolgtekst_raw`, `nieuws`.`toon` AS `nieuws___toon`, `nieuws`.`toon` AS `nieuws___toon_raw`, `nieuws`.`Volgorde` AS `nieuws___Volgorde`, `nieuws`.`Volgorde` AS `nieuws___Volgorde_raw`, `nieuws_27_repeat`.`id` AS `nieuws_27_repeat___id`, `nieuws_27_repeat`.`id` AS `nieuws_27_repeat___id_raw`, `nieuws_27_repeat`.`parent_id` AS `nieuws_27_repeat___parent_id`, `nieuws_27_repeat`.`parent_id` AS `nieuws_27_repeat___parent_id_raw`, `nieuws_27_repeat`.`afbeelding` AS `nieuws_27_repeat___afbeelding`, `nieuws_27_repeat`.`afbeelding` AS `nieuws_27_repeat___afbeelding_raw`, `nieuws_27_repeat`.`alt` AS `nieuws_27_repeat___alt`, `nieuws_27_repeat`.`alt` AS `nieuws_27_repeat___alt_raw`, `nieuws_27_repeat`.`opschrift` AS `nieuws_27_repeat___opschrift`, `nieuws_27_repeat`.`opschrift` AS `nieuws_27_repeat___opschrift_raw`, `nieuws_28_repeat`.`id` AS `nieuws_28_repeat___id`, `nieuws_28_repeat`.`id` AS `nieuws_28_repeat___id_raw`, `nieuws_28_repeat`.`poster` AS `nieuws_28_repeat___poster`, `nieuws_28_repeat`.`poster` AS `nieuws_28_repeat___poster_raw`, `nieuws_28_repeat`.`youtube` AS `nieuws_28_repeat___youtube`, `nieuws_28_repeat`.`youtube` AS `nieuws_28_repeat___youtube_raw`, `nieuws_28_repeat`.`parent_id` AS `nieuws_28_repeat___parent_id`, `nieuws_28_repeat`.`parent_id` AS `nieuws_28_repeat___parent_id_raw`, `nieuws`.`Banner` AS `nieuws___Banner_raw`, `kmpn_call_to_action`.`Titel` AS `nieuws___Banner`, `nieuws`.`Related_Content` AS `nieuws___Related_Content_raw`, `nieuws_0`.`titel` AS `nieuws___Related_Content`, `nieuws`.`Related_Content_Agenda` AS `nieuws___Related_Content_Agenda_raw`, `kmpn_agenda`.`Titel` AS `nieuws___Related_Content_Agenda`, `nieuws`.`locatie` AS `nieuws___locatie`, `nieuws`.`locatie` AS `nieuws___locatie_raw`, `nieuws`.`straat_huisnummer` AS `nieuws___straat_huisnummer`, `nieuws`.`straat_huisnummer` AS `nieuws___straat_huisnummer_raw`, `nieuws`.`postcode` AS `nieuws___postcode`, `nieuws`.`postcode` AS `nieuws___postcode_raw`, `nieuws`.`plaats` AS `nieuws___plaats`, `nieuws`.`plaats` AS `nieuws___plaats_raw`, `nieuws`.`startdatum` AS `nieuws___startdatum`, `nieuws`.`startdatum` AS `nieuws___startdatum_raw`, `nieuws`.`einddatum` AS `nieuws___einddatum`, `nieuws`.`einddatum` AS `nieuws___einddatum_raw`, `nieuws`.`gepubliceerd` AS `nieuws___gepubliceerd`, `nieuws`.`gepubliceerd` AS `nieuws___gepubliceerd_raw`, `nieuws`.`id` AS slug , `nieuws`.`id` AS `__pk_val` FROM `nieuws` LEFT JOIN `nieuws_27_repeat` AS `nieuws_27_repeat` ON `nieuws_27_repeat`.`parent_id` = `nieuws`.`id` LEFT JOIN `nieuws_28_repeat` AS `nieuws_28_repeat` ON `nieuws_28_repeat`.`parent_id` = `nieuws`.`id` LEFT JOIN `kmpn_call_to_action` AS `kmpn_call_to_action` ON `kmpn_call_to_action`.`id` = `nieuws`.`Banner` LEFT JOIN `nieuws` AS `nieuws_0` ON `nieuws_0`.`id` = `nieuws`.`Related_Content` LEFT JOIN `kmpn_agenda` AS `kmpn_agenda` ON `kmpn_agenda`.`id` = `nieuws`.`Related_Content_Agenda` WHERE `nieuws`.`id` IN ('423','374','302','281','273') ORDER BY `nieuws`.`id` DESC

I added the pre-filters as a screenshot.
The fields are in dutch (the language of the site), to make things easier, 'Gepubliceerd' (first field) is an yesno option to make publish/unpublish the item.
'Startdatum' is the starting date to show the item (if it's empty it should be visible if the yesno has a yes value) and 'Einddatum' is the date it should stop publishing (again, if empty, it can't unpublish by date)

 

[cheesegrits]

There should be another debug entry with the query right before the getData one that has the where clause for those filters in it. I'm away from my computer at the moment, I can't remember what it's called, but it's the one invoked when you are merging joined data. Can you locate that and paste that query?

 

[machelp]

You mean the mergeJoinedData?
The query in there:

Spoiler

SELECT DISTINCT `nieuws`.`id` AS __pk_val0, `nieuws_27_repeat`.`id` AS __pk_val1, `nieuws_28_repeat`.`id` AS __pk_val2 FROM `nieuws` LEFT JOIN `nieuws_27_repeat` AS `nieuws_27_repeat` ON `nieuws_27_repeat`.`parent_id` = `nieuws`.`id` LEFT JOIN `nieuws_28_repeat` AS `nieuws_28_repeat` ON `nieuws_28_repeat`.`parent_id` = `nieuws`.`id` LEFT JOIN `kmpn_call_to_action` AS `kmpn_call_to_action` ON `kmpn_call_to_action`.`id` = `nieuws`.`Banner` LEFT JOIN `nieuws` AS `nieuws_0` ON `nieuws_0`.`id` = `nieuws`.`Related_Content` LEFT JOIN `kmpn_agenda` AS `kmpn_agenda` ON `kmpn_agenda`.`id` = `nieuws`.`Related_Content_Agenda` WHERE ( (nieuws.gepubliceerd = '1' OR nieuws.gepubliceerd LIKE '[\"1\"%' OR nieuws.gepubliceerd LIKE '%\"1\"%' OR nieuws.gepubliceerd LIKE '%\"1\"]') AND ( nieuws.startdatum = ('0000-00-00 00:00:00') OR nieuws.startdatum <= (NOW()) ) AND ( nieuws.einddatum = ('0000-00-00 00:00:00') OR nieuws.einddatum >= (NOW()) ) ) ORDER BY `nieuws`.`id` DESC

 

[troester]

Yes, this one.
Is the WHERE part what you are expecting?
Is an empty date really 0000-00-00 00:00:00 in your DB?

 

[machelp]

Yeah, the where is as i should need it (only the 'gepubliceerd' is acting a little wierd, but it seems to work).
If i open the table in PHPMyAdmin, i see 0000-00-00 00:00:00 as date (which should be a default MySQL thing, if i'm not mistaking).
I also tried the IS NULL option or as value just '0', but that didn't work either.

 

[troester]

So what is not working?
It's returning the records with ids ('423','374','302','281','273') (in your first example)

 

[machelp]

If ?m quering the exact query i get from Fabrik in PhpMyAdmin, it does, yes.
But if i'm loading the data in Fabrik itself (to show), it doesn't show the items i need.
Specifically the items with 0000-00-00 00:00:00 values won't show at all..

 

[cheesegrits]

I'll have to log in to your site to see what's going on. Can you fill out your My Sites info.

But just to confirm what you seem to be saying:

If you run that query with the WHERE in it for the dates (post #5) by hand in phpMyAdmin, those id's it returns are the correct one that you would expect?

if you then run the second query by hand (post #3), again it return the rows and data you would expect?

But then in your actual Fabrik app, it doesn't show the correct rows (it's missing the null date ones)?

-- hugh

 

[machelp]

If i run the query in post #5, it returns one of the articles i'm testing with (has id 423), yes.
The second query, in post #3 returns the rows i expect (id 423 is in the results).
In Fabrik (the page) it's indeed missing the rows with null dates.

I filled in the My Site form, the site itself is dutch, i've set the language of the backend to english, which i think would be easier for you.

 

[machelp]

A friendly reminder that i'm still having the issue and waiting for a response..