Uploaded image for project: 'CiviCRM'
  1. CiviCRM
  2. CRM-15609

Between date filter does not work in views, if inside an OR block

    Details

    • Type: Bug
    • Status: Done/Fixed
    • Priority: Trivial
    • Resolution: Fixed/Completed
    • Affects Version/s: 4.5.3
    • Fix Version/s: 4.5.5
    • 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

      {civicrm_membership} civicrm_membership
      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}

      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') 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

        Attachments

          Activity

            People

            • Assignee:
              jitendra.purohit Jitendra Purohit
              Reporter:
              tisoft_media Markus Heberling
            • Votes:
              0 Vote for this issue
              Watchers:
              2 Start watching this issue

              Dates

              • Created:
                Updated:
                Resolved: