Details
-
Type: Bug
-
Status: Done/Fixed
-
Priority: Trivial
-
Resolution: Fixed/Completed
-
Affects Version/s: 4.5.3
-
Fix Version/s: 4.5.5
-
Component/s: Drupal Integration Modules
-
Labels:None
-
Documentation Required?:None
Description
I want to have several between date filters inside an or block in a view. Unfortunately it uses the type of the block to generate the AND/OR in the between where clause:
SELECT civicrm_membership.id AS id, civicrm_contact.id AS civicrm_contact_id, civicrm_contact.first_name AS civicrm_contact_first_name, civicrm_contact.last_name AS civicrm_contact_last_name, civicrm_address.street_address AS civicrm_address_street_address, civicrm_address.postal_code AS civicrm_address_postal_code, civicrm_address.city AS civicrm_address_city, civicrm_contact.birth_date AS civicrm_contact_birth_date, civicrm_email.email AS civicrm_email_email, civicrm_membership.membership_type_id AS civicrm_membership_membership_type_id, civicrm_membership.join_date AS civicrm_membership_join_date, civicrm_phone.phone AS civicrm_phone_phone, civicrm_phone2.phone AS civicrm_phone2_phone, civicrm_contact.is_deceased AS civicrm_contact_is_deceased, civicrm_value_jubil_um_8.jubil_um_10_jahre_21 AS civicrm_value_jubil_um_8_jubil_um_10_jahre_21, civicrm_value_jubil_um_8.jubil_um_20_jahre_22 AS civicrm_value_jubil_um_8_jubil_um_20_jahre_22, civicrm_value_jubil_um_8.jubil_um_25_jahre_23 AS civicrm_value_jubil_um_8_jubil_um_25_jahre_23, civicrm_value_jubil_um_8.jubil_um_30_jahre_24 AS civicrm_value_jubil_um_8_jubil_um_30_jahre_24, civicrm_value_jubil_um_8.jubil_um_40_jahre_25 AS civicrm_value_jubil_um_8_jubil_um_40_jahre_25, civicrm_value_jubil_um_8.jubil_um_50_jahre_26 AS civicrm_value_jubil_um_8_jubil_um_50_jahre_26, civicrm_value_jubil_um_8.jubil_um_60_jahre_27 AS civicrm_value_jubil_um_8_jubil_um_60_jahre_27, DATE_FORMAT((civicrm_membership.start_date + INTERVAL 3600 SECOND), '%Y') AS civicrm_membership_start_date_year
FROM
LEFT JOIN {civicrm_value_jubil_um_8} civicrm_value_jubil_um_8 ON civicrm_membership.id = civicrm_value_jubil_um_8.entity_id
LEFT JOIN {civicrm_contact} civicrm_contact ON civicrm_membership.contact_id = civicrm_contact.id
LEFT JOIN {civicrm_address} civicrm_address ON civicrm_contact.id = civicrm_address.contact_id
LEFT JOIN {civicrm_email} civicrm_email ON civicrm_contact.id = civicrm_email.contact_id AND civicrm_email.is_primary = '1'
LEFT JOIN {civicrm_phone} civicrm_phone ON civicrm_contact.id = civicrm_phone.contact_id AND (civicrm_phone.location_type_id = '1' AND civicrm_phone.phone_type_id = '1')
LEFT JOIN {civicrm_phone} civicrm_phone2 ON civicrm_contact.id = civicrm_phone2.contact_id AND (civicrm_phone2.location_type_id = '1' AND civicrm_phone2.phone_type_id = '2')
WHERE (( (civicrm_membership.membership_type_id IN ('1', '2', '3', '4', '6')) AND (civicrm_membership.status_id IN ('2')) )AND( (civicrm_value_jubil_um_8.jubil_um_10_jahre_21 >= '2013-01-01 00:00:00') OR (civicrm_value_jubil_um_8.jubil_um_10_jahre_21 <= '2015-12-31 00:00:00') OR (civicrm_value_jubil_um_8.jubil_um_20_jahre_22 >= '2014-01-01 00:00:00') OR (civicrm_value_jubil_um_8.jubil_um_20_jahre_22 <= '2015-12-31 00:00:00') ))
ORDER BY civicrm_membership_membership_type_id ASC, civicrm_membership_start_date_year DESC
I would have expectet this:
SELECT civicrm_membership.id AS id, civicrm_contact.id AS civicrm_contact_id, civicrm_contact.first_name AS civicrm_contact_first_name, civicrm_contact.last_name AS civicrm_contact_last_name, civicrm_address.street_address AS civicrm_address_street_address, civicrm_address.postal_code AS civicrm_address_postal_code, civicrm_address.city AS civicrm_address_city, civicrm_contact.birth_date AS civicrm_contact_birth_date, civicrm_email.email AS civicrm_email_email, civicrm_membership.membership_type_id AS civicrm_membership_membership_type_id, civicrm_membership.join_date AS civicrm_membership_join_date, civicrm_phone.phone AS civicrm_phone_phone, civicrm_phone2.phone AS civicrm_phone2_phone, civicrm_contact.is_deceased AS civicrm_contact_is_deceased, civicrm_value_jubil_um_8.jubil_um_10_jahre_21 AS civicrm_value_jubil_um_8_jubil_um_10_jahre_21, civicrm_value_jubil_um_8.jubil_um_20_jahre_22 AS civicrm_value_jubil_um_8_jubil_um_20_jahre_22, civicrm_value_jubil_um_8.jubil_um_25_jahre_23 AS civicrm_value_jubil_um_8_jubil_um_25_jahre_23, civicrm_value_jubil_um_8.jubil_um_30_jahre_24 AS civicrm_value_jubil_um_8_jubil_um_30_jahre_24, civicrm_value_jubil_um_8.jubil_um_40_jahre_25 AS civicrm_value_jubil_um_8_jubil_um_40_jahre_25, civicrm_value_jubil_um_8.jubil_um_50_jahre_26 AS civicrm_value_jubil_um_8_jubil_um_50_jahre_26, civicrm_value_jubil_um_8.jubil_um_60_jahre_27 AS civicrm_value_jubil_um_8_jubil_um_60_jahre_27, DATE_FORMAT((civicrm_membership.start_date + INTERVAL 3600 SECOND), '%Y') AS civicrm_membership_start_date_year
FROM {civicrm_membership}
civicrm_membership
LEFT JOIN
civicrm_value_jubil_um_8 ON civicrm_membership.id = civicrm_value_jubil_um_8.entity_id
LEFT JOIN
civicrm_contact ON civicrm_membership.contact_id = civicrm_contact.id
LEFT JOIN
civicrm_address ON civicrm_contact.id = civicrm_address.contact_id
LEFT JOIN
civicrm_email ON civicrm_contact.id = civicrm_email.contact_id AND civicrm_email.is_primary = '1'
LEFT JOIN
LEFT JOIN {civicrm_phone}
civicrm_phone2 ON civicrm_contact.id = civicrm_phone2.contact_id AND (civicrm_phone2.location_type_id = '1' AND civicrm_phone2.phone_type_id = '2')
WHERE (( (civicrm_membership.membership_type_id IN ('1', '2', '3', '4', '6')) AND (civicrm_membership.status_id IN ('2')) )AND( (civicrm_value_jubil_um_8.jubil_um_10_jahre_21 >= '2013-01-01 00:00:00') AND (civicrm_value_jubil_um_8.jubil_um_10_jahre_21 <= '2015-12-31 00:00:00') OR (civicrm_value_jubil_um_8.jubil_um_20_jahre_22 >= '2014-01-01 00:00:00') AND (civicrm_value_jubil_um_8.jubil_um_20_jahre_22 <= '2015-12-31 00:00:00') ))
ORDER BY civicrm_membership_membership_type_id ASC, civicrm_membership_start_date_year DESC