List filter issue

[monkframes]

Hi guys,

I have a problem with a filtered list, I?ll try to explain step by step:

- I choose a date as a filter and the list displays correctly the filtered data.
- I modify some of the data through the linked form and save with no problems.
- When I choose any other date in the filter, it don?t change the filtered data.
- If I try to reload the list page it gives me an error 500 page, I show you below. The error I think is down at the WHERE clause, it ts not complete an the date shown is not the date selected in the filter.

Please help me !!!



Fabrik has generated an incorrect query for the list Servicios:

SQL=SELECT SQL_CALC_FOUND_ROWS DISTINCT `servicios`.`id` AS `servicios___id`,
`servicios`.`id` AS `servicios___id_raw`,
`servicios`.`fecha_alta` AS `servicios___fecha_alta`,
`servicios`.`fecha_alta` AS `servicios___fecha_alta_raw`,
`servicios`.`id_conductor` AS `servicios___id_conductor_raw`,
`conductores`.`conductor` AS `servicios___id_conductor`,
`servicios`.`fecha_inicio` AS `servicios___fecha_inicio`,
`servicios`.`fecha_inicio` AS `servicios___fecha_inicio_raw`,
`servicios`.`hora_inicio` AS `servicios___hora_inicio`,
`servicios`.`hora_inicio` AS `servicios___hora_inicio_raw`,
`servicios`.`id_tipo_de_vehiculo` AS `servicios___id_tipo_de_vehiculo_raw`,
`tipos_de_vehiculo`.`tipo_vehiculo` AS `servicios___id_tipo_de_vehiculo`,
`servicios`.`vehiculo` AS `servicios___vehiculo_raw`,
`vehiculos`.`matricula` AS `servicios___vehiculo`,
`servicios`.`id_colaborador` AS `servicios___id_colaborador_raw`,
`conductores_0`.`id_colaborador` AS `servicios___id_colaborador`,
`servicios`.`fecha_fin` AS `servicios___fecha_fin`,
`servicios`.`fecha_fin` AS `servicios___fecha_fin_raw`,
`servicios`.`hora_fin` AS `servicios___hora_fin`,
`servicios`.`hora_fin` AS `servicios___hora_fin_raw`,
`servicios`.`id_tipo_de_servicio` AS `servicios___id_tipo_de_servicio_raw`,
`tipos_de_servicio`.`tipo_servicio` AS `servicios___id_tipo_de_servicio`,
`servicios`.`origen` AS `servicios___origen`,
`servicios`.`origen` AS `servicios___origen_raw`,
`servicios`.`destino` AS `servicios___destino`,
`servicios`.`destino` AS `servicios___destino_raw`,
`servicios`.`pasajeros` AS `servicios___pasajeros`,
`servicios`.`pasajeros` AS `servicios___pasajeros_raw`,
`servicios`.`id_cliente` AS `servicios___id_cliente_raw`,
`clientes`.`cliente` AS `servicios___id_cliente`,
`servicios`.`id_grupo` AS `servicios___id_grupo_raw`,
`grupos`.`grupo` AS `servicios___id_grupo`,
`servicios`.`notas` AS `servicios___notas`,
`servicios`.`notas` AS `servicios___notas_raw`,
`servicios`.`importe` AS `servicios___importe`,
`servicios`.`importe` AS `servicios___importe_raw`,
`servicios`.`id_factura` AS `servicios___id_factura`,
`servicios`.`id_factura` AS `servicios___id_factura_raw`,
`servicios`.`log` AS `servicios___log`,
`servicios`.`log` AS `servicios___log_raw`,
`servicios`.`estado` AS `servicios___estado`,
`servicios`.`estado` AS `servicios___estado_raw`,
`servicios`.`id` AS slug
, `servicios`.`id` AS `__pk_val`

FROM `servicios`
LEFT JOIN `tipos_de_servicio` AS `tipos_de_servicio` ON `tipos_de_servicio`.`id` = `servicios`.`id_tipo_de_servicio`
LEFT JOIN `clientes` AS `clientes` ON `clientes`.`id` = `servicios`.`id_cliente`
LEFT JOIN `grupos` AS `grupos` ON `grupos`.`id` = `servicios`.`id_grupo`
LEFT JOIN `tipos_de_vehiculo` AS `tipos_de_vehiculo` ON `tipos_de_vehiculo`.`id` = `servicios`.`id_tipo_de_vehiculo`
LEFT JOIN `conductores` AS `conductores` ON `conductores`.`id` = `servicios`.`id_conductor`
LEFT JOIN `vehiculos` AS `vehiculos` ON `vehiculos`.`id` = `servicios`.`vehiculo`
LEFT JOIN `conductores` AS `conductores_0` ON `conductores_0`.`id_colaborador` = `servicios`.`id_colaborador`
WHERE ( `servicios`.`fecha_inicio` between 2014-03-18 23:00:00 )
ORDER BY `servicios`.`hora_inicio` ASC, `servicios`.`hora_inicio` ASC

 

[alastairwalker]

If you are familiar with PHPADMIN, then enter the query under the SQL tab (in phpadmin) and use the error reporting facilities of the tool to tell you where the sql error is.

I always use this approach when a 500 error occurs. It is an excellent method to find the sql error source.

Alastair

 

[monkframes]

Thanks Alastair, actually I did it, that?s why I think the error is in the construction of the query, at the WHERE clause, and there is no sense that the filter works perfectly before but not after you modify some data.

PhpMyAdmin returned:
#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '23:00:00 ) ORDER BY `servicios`.`hora_inicio` ASC, `servicios`.`hora_inicio` ASC' at line 54

I?m not an sql expert but I think the syntax must be "BETWEEN date1 AND date2" and for some reason it seems that after a data modification, the filter changes "date1" and "AND date2" is missing.

Thanks again.

 

[troester]

I think the syntax must be "BETWEEN date1 AND date2"

Yes.

What is your setup?
- any list prefilters (in list or menu)
- which date filter settings
- Joomla caching
- list ajaxfied

 

[alastairwalker]

I agree with the comments from troester, but I also have the following comments to make. The FB query builder is an extremely complex piece of logic, and I think sometimes it slips up on very complex table relationships.

I recommend that you create an extremely simple table (i.e. the two basic fields plus one other) and test out the filter condition in this minimal arrangement. If the query builder creates an incorrect query under these conditions, steps can be taken to explore why the query is being assembled incorrectly.

I have had experience where the query builder works correctly under minimal conditions, but falls over (for whatever reason) in the more complex situation.

I have found on occasion that rebuilding the 'complex query' from scratch (i.e. new list/form etc) from the underlying tables has eliminated the source of the problem.

Alastair

 

[monkframes]

Thank you both,

List prefilters: yes in list, but if I eliminate them the problem persists, in menu, everything disabled
Date filter settings: drop down, exact match, and neccesary
Joomla caching: disabled in system global configurtion and System Cache Plugin disabled
List ajaxify: enabled in navigation tab and disabled in links tab, but if I deactivate both the problem persists (by the way, I had problems with ajaxify in the links tab and also with data group and I decided not to use them)

I was poking around with almost every option last couple of days, I?m going to play with menu prefilters and try with a simple table as you suggested and I?ll come back with the results.

Thanks again.

 

[troester]

Yes, there seem to be issues with date filtering.
I get (latest Git)
WHERE ( `test`.`date_time` BETWEEN "[[:<:]]'2013-05-22 20:00:00' AND '2013-05-23 19:59:59'[[:>:]]"

"Normal" date is filtered BETWEEN xx 00:0000 AND xx 23:59:59
Beside the wrong string it seems here is again a double UTC time offset (date is UTC, my server/Joomla time is UTC+2)

In case of dropdown + exact there shouldn't be a BETWEEN.

 

[monkframes]

I have made some progress: if I say "Delete Filters YES" at Fabrik List Options tab in the menu element, everything works again.

I would prefer to have "Delete Filters" as "NO" so when I save the changes in the Form, it will return to the list and display the data of the date selected, now I have to choose the date again, but, anyway, this is not the least of my problems.

Thanks again guys for your clues.