If I try to filter on a relationship date field that is configured with a timezone the sql query tries to add the date offset using the original field without relationship, which obviously breaks the query.

Here's the output query of my view:

SELECT DISTINCT(node.nid) AS nid,
   node_data_field_event_ld_dates.field_event_ld_dates_value AS node_data_field_event_ld_dates_field_event_ld_dates_value
 FROM node node 
 LEFT JOIN content_field_event_ld node_data_field_event_ld ON node.vid = node_data_field_event_ld.vid
 INNER JOIN node node_node_data_field_event_ld ON node_data_field_event_ld.field_event_ld_nid = node_node_data_field_event_ld.nid
 LEFT JOIN private private ON node.nid = private.nid
 LEFT JOIN content_field_event_ld_dates node_node_data_field_event_ld__node_data_field_event_ld_dates ON node_node_data_field_event_ld.vid = node_node_data_field_event_ld__node_data_field_event_ld_dates.vid
 LEFT JOIN content_field_event_ld_dates node_data_field_event_ld_dates ON node.vid = node_data_field_event_ld_dates.vid
 WHERE ((node.type in ('event')) AND (node.status <> 0) AND (private.private = 0 OR private.private IS NULL))
    AND <strong>(DATE_FORMAT(ADDTIME(node_node_data_field_event_ld__node_data_field_event_ld_dates.field_event_ld_dates_value, SEC_TO_TIME(node_data_field_event_ld_dates.field_event_ld_dates_offset)), '%Y-%m-%d') >= '2011-04-27')</strong>
 GROUP BY nid
  ORDER BY node_data_field_event_ld_dates_field_event_ld_dates_value ASC

Note that it tries to add the offset from node_data_field_event_ld_dates.field_event_ld_dates_offset to node_node_data_field_event_ld__node_data_field_event_ld_dates.field_event_ld_dates_value, while it should be adding it from node_node_data_field_event_ld__node_data_field_event_ld_dates.field_event_ld_dates_offset

I've looked into the module's code, but can't figure out how to fix this.
Any help is highly appreciated.

Thank you.

---
Oussama Mubarak // Semiaddict

Comments

mstef’s picture

+1

---

SELECT DISTINCT(signup_log.sid) AS sid,
node_signup_log.title AS node_signup_log_title,
node_signup_log.nid AS node_signup_log_nid,
node_signup_log__node_data_field_event_date.field_event_date_value AS date
FROM signup_log signup_log
LEFT JOIN node node_signup_log ON signup_log.nid = node_signup_log.nid
LEFT JOIN users users_signup_log ON signup_log.uid = users_signup_log.uid
LEFT JOIN content_field_event_date node_signup_log__node_data_field_event_date ON node_signup_log.vid = node_signup_log__node_data_field_event_date.vid
LEFT JOIN content_field_event_date node_signup_log_node_data_field_event_date ON node_signup_log.vid = node_signup_log_node_data_field_event_date.vid
WHERE ((node_signup_log.status = 1)
AND (users_signup_log.uid = 1 ))
AND (DATE_FORMAT(ADDTIME(node_signup_log__node_data_field_event_date.field_event_date_value, SEC_TO_TIME(node_data_field_event_date.field_event_date_offset)), '%Y-%m-%d') >= '2011-06-08')
ORDER BY node_signup_log_node_data_field_event_date_field_event_date_ ASC

damienmckenna’s picture

Status: Active » Closed (won't fix)

Unfortunately the Drupal 6 version of the Date module is no longer supported. That said, we appreciate that you took time to work on this issue. Should this request still be relevant for Drupal 7 please feel free to reopen it. Thank you.