0

I have a view for a content type. This content type has a date field (only date) and I have added and exposed a filter for this field ("date is between" option). I have added and exposed a filter for other text field.
If I put a condition on the text field in the filter of the view, then it generate a weird SQL for the date field.

SELECT node_field_data.nid AS nid, node_field_data_node__field_liq_club.nid AS node_field_data_node__field_liq_club_nid
FROM
node_field_data node_field_data
LEFT JOIN node__field_liq_club node__field_liq_club ON node_field_data.nid = node__field_liq_club.entity_id AND node__field_liq_club.deleted = '0'
INNER JOIN node_field_data node_field_data_node__field_liq_club ON node__field_liq_club.field_liq_club_target_id = node_field_data_node__field_liq_club.nid
LEFT JOIN node__field_liq_ejercicio node__field_liq_ejercicio ON node_field_data.nid = node__field_liq_ejercicio.entity_id AND node__field_liq_ejercicio.deleted = '0'
LEFT JOIN node__field_liq_fechaliq node__field_liq_fechaliq ON node_field_data.nid = node__field_liq_fechaliq.entity_id AND node__field_liq_fechaliq.deleted = '0'
LEFT JOIN node__field_liq_fechapago node__field_liq_fechapago ON node_field_data.nid = node__field_liq_fechapago.entity_id AND node__field_liq_fechapago.deleted = '0'
WHERE
(node_field_data.status = '1') AND (node_field_data.type IN ('liquidacion')) AND (node__field_liq_ejercicio.field_liq_ejercicio_value = '2010') AND 
((DATE_FORMAT(node__field_liq_fechaliq.field_liq_fechaliq_value, '%Y-%m-%d') BETWEEN DATE_FORMAT('2019-01-01T00:00:00', '%Y-%m-%d') AND DATE_FORMAT('2019-12-31T00:00:00', '%Y-%m-%d'))) 
AND ((DATE_FORMAT((node__field_liq_fechapago.field_liq_fechapago_value + INTERVAL 7200 SECOND), '%Y-%m-%d\T%H:%i:%s') BETWEEN DATE_FORMAT(('2018-12-31T23:00:00' + INTERVAL 7200 SECOND), '%Y-%m-%d\T%H:%i:%s') AND DATE_FORMAT(('2019-12-30T23:00:00' + INTERVAL 7200 SECOND), '%Y-%m-%d\T%H:%i:%s')))
LIMIT 25 OFFSET 0

My text field add the condition:
node__field_liq_ejercicio.field_liq_ejercicio_value = '2010'

But, although I don't fill any condition for date-range, it adds:

    and ((DATE_FORMAT(node__field_liq_fechaliq.field_liq_fechaliq_value, '%Y-%m-%d') BETWEEN DATE_FORMAT('2019-01-01T00:00:00', '%Y-%m-%d') AND DATE_FORMAT('2019-12-31T00:00:00', '%Y-%m-%d'))) 
AND ((DATE_FORMAT((node__field_liq_fechapago.field_liq_fechapago_value + INTERVAL 7200 SECOND), '%Y-%m-%d\T%H:%i:%s') BETWEEN DATE_FORMAT(('2018-12-31T23:00:00' + INTERVAL 7200 SECOND), '%Y-%m-%d\T%H:%i:%s') AND DATE_FORMAT(('2019-12-30T23:00:00' + INTERVAL 7200 SECOND), '%Y-%m-%d\T%H:%i:%s')))

I don't know why it is added, and how to solve this issue. Why this INTERVAL 7200 SECOND?. Any suggestion?

0

After some test I have found that a module was changing filter form states, and then the filter fields were wrong when the query were constructed. I fix that and now it works good.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.