Prefilter on joined list returns error

[Ecovoyageurs]

Hi,

I have 2 lists : "trips" and "departures".
"departures" is left joined to "trips".

I tried to set a prefilter in trips list > Data > Prefilters : WHERE departures.dep_date GREATER THAN NOW() No quotes Public
- the prefilter works on the trips list view
- the prefilter works on the trips detail view if there is at least one departure for the trip
- the prefilter doesn't work if there is no departure for the trip. It returns "we cannot find this..." on the detail view... and nothing is shown.

You can see it on my dev demo site > front > Nos voyages > Safari en famille.

Thanks for any help with this !

 

[cheesegrits]

That's as it should be. If a field you are using in the WHERE clause doesn't exist, the test is going to return false.

If you were writing the query by hand, the way to do it would be to use COALESCE(), something like "WHERE COALESCE(departures.dep_date, NOW()-1) > NOW()", which would use NOW()-1 if dep_date was NULL. But the pre-filter builder doesn't have that concept in it.

You might be able to work round it with adding a second pre-filter, OR departures.dep_date IS NULL, grouped. Let me know if that doesn't work.

BTW, looking at the query we are generating for that page, it looks like this might not be the only issues, there's a "5=-5" in there as well, which is generated when there is a checkbox join with no selections.

-- hugh

 

[Ecovoyageurs]

Hi Hugh, and thanks for your answer,
I filled the empty checkbox join and tried the second prefilter. Unfortunately it didn't solve the problem. I still get the same result... You can see it on the same page.

Is there another way to use COALESCE ?
I tried in the pre-filter query field but got an SQL error...

 

[cheesegrits]

There's a lot going on on that page ...

Can you create a simpler test page for it that only displays that one detail view, from a menu link?

-- hugh

 

[Ecovoyageurs]

Yep... next step is to optimize...
I added a page in the mainmenu called "test list join" and added this with bootstrap template.

 

[Ecovoyageurs]

Hi,
Did you have time to throw an eye ?
Thanks

 

[cheesegrits]

"Nous sommes incapables de trouver cet enregistrement"

-- hugh

 

[Ecovoyageurs]

Well yeah, I said

It returns "we cannot find this..." on the detail view

but my site is in French, so same thing but in French...

If I remove the prefilter, the trip information show up.

 

[Ecovoyageurs]

I got it solved using the content plugin... which just returns nothing when there's no row
Just a question : is it heavier (for the page's queries) to call a list with the content plugin than to create a list join ?

 

[cheesegrits]

Embedding multiple content plugins in one article does create an overhead, yes, as it basically has to build each Fabrik "thing" from first principles, loading the entire Fabrik component from scratch each time, reading all the metadata tables, etc. That's just the way J! plugins work, nothing we can do about it. It's pretty much the same as if you were loading your Fabrik "thing" though a URL, with &tmpl=component, to render just the component output, without the rest of the J! page content.

-- hugh

 

[Ecovoyageurs]

Oops, I have 6 content plugins on my frontpage and 5 on my detail pages... seems like I'll need to look for a good cache extension !

 

[cheesegrits]

I wouldn't. Caching doesn't mix with Fabrik very well. It's OK in certain circumstances, but most aggressive caching will cause problems, as the caching only looks at the URL to determine if it's the "same" page, whereas we use things like session data to carry changes between page loads.

You can try, but be on the lookout for odd behaviour.

-- hugh