Problem/Motivation

We want to be able to use the opIncludes operator with potentially NULL start or end values.

NULL start or end values are possible when you use the Optional End Date module.

Steps to reproduce

The BETWEEN operator used in the query will exclude all results with NULL start or end values.

Proposed resolution

Replace BETWEEN SQL operator with this simple WHERE condition:

select *
  from m_table
 where ( data >= :start_date
         or :start_date is null
          )
   and ( date <= :end_date
         or :end_date is null
          )

The second operator duplicates the first but generates an index-friendly SQL query by avoiding the DATE_FORMAT function for better performance.

User interface changes

The two new operators added.

Command icon Show commands

Start within a Git clone of the project using the version control instructions.

Or, if you do not have SSH keys set up on git.drupalcode.org:

Comments

mably created an issue. See original summary.

mably’s picture

Added a second version of the "Includes Unbound" operator allowing usage of the date range fields indexes.

mably’s picture

Status: Active » Needs review
mably’s picture

Title: Add a new operator to handle NULL start or end values » Add two new operators to properly handle NULL start or end values
Issue summary: View changes

  • mably committed c7274a63 on 2.x
    feat: #3562035 Add two new operators to properly handle NULL start or...
mably’s picture

Version: 8.x-1.x-dev » 2.x-dev
Status: Needs review » Fixed

Now that this issue is closed, review the contribution record.

As a contributor, attribute any organization that helped you, or if you volunteered your own time.

Maintainers, credit people who helped resolve this issue.

Status: Fixed » Closed (fixed)

Automatically closed - issue fixed for 2 weeks with no activity.