List join and url filters

[Ecovoyageurs]

Hi,
I have a trip list called fk_voyages. On this list, I have a LEFT JOIN on a table called fk_groupes (= departures).
It returns :
Trip 1
- departure 1 on 2018-07-26
- departure 2 on 2018-07-30

Trip 2
- departure 1 on 2018-07-24

I now would like to filter the list (url filter) on the departure date, but I would like the result to all trips, even if they don't have any departure.
My url filter is like :
...fk_groupes___start_date[value][0]=2018-07-25 00:00:00&fk_groupes___start_date[value][1]=2018-07-26 00:00:00&fk_groupes___start_date[condition]=BETWEEN

And it would return :
Trip 1
- departure 1 on 2018-07-26

Trip 2

How is this possible ?
The thing is that even if the JOIN is LEFT, the list only returns a trip that has at least 1 departure.
Ex : In the previous exemple it doesn't show the trip 2.

Thanks for any help !
Theo

 

[cheesegrits]

Well, yes , that's as expected. If you look at the query that generates using &fabrikdebug=1, look at the getData query, you'll see it's adding something like ...

AND fk_groupes___start_date BETWEEN 2018-07-25 00:00:00 AND 2018-07-26 00:00:00

... so it's doing exactly what you asked it to do. Rows that don't have a departure don't join any fk_groupes rows, and so that condition isn't met.

To include trips that don't have any departures you'd probably need to do something like adding an additional filter ...

&fk_groupes___id[join]=OR&fk_groupes___id[condition]=IS%20NULL

... which should include rows where there is no join, so the 'fk_groupes___id' field is NULL, so the WHERE clause looks like ...

AND fk_groupes___start_date BETWEEN 2018-07-25 00:00:00 AND 2018-07-26 00:00:00 OR fk_groupes___id IS NULL

-- hugh