How can I query for entries that have one of two Lightswitch fields enabled?

Question

I have two Lightswitch fields – lightswitchA and lightswitchB – and I need to pull all entries in a certain section, that have one or both of these fields enabled.

Is it possible to execute such a query with a standard ElementCriteriaModel, or do I need to construct a custom query? If I need to construct a custom query, how would that look (in a template, not a plugin)?

I also need to paginate the results, using the {% paginate %} tag.

Answer 1

Craft 3

Thanks to the where() function this has become easy in Craft 3.

{% set query = craft.entries.where([
    'or',
    {'field_lightswitchA': 1},
    {'field_lightswitchB': 1},
]) %}

Craft 2

Custom field parameters are being added with and-logic, so you'd indeed have to do it from PHP with custom dbCommands.

You could either approach it with converting a criteria model to a dbCommand object using the buildElementsQuery method, or you listen for elements.buildElementsQuery events and modify the dbCommand if the criteria model meets certain conditions.

craft()->on('elements.buildElementsQuery', function(Event $event) {
    $criteria = $event->params['criteria'];
    $dbCommand = $event->params['query'];

    if (isset($criteria->or_lightswitchA) && isset($criteria->or_lightswitchB)) {
        $params = array(
            ':lightswitchA_value' => $criteria->or_lightswitchA,
            ':lightswitchB_value' => $criteria->or_lightswitchB,
        );

        $conditions = array('or',
            'content.field_lightswitchA=:lightswitchA_value',
            'content.field_lightswitchB=:lightswitchB_value',
        );

        $dbCommand->andWhere($conditions, $params);
    }
});

Here's how you'd trigger that listener from your criteria model in Twig.

{% set entries = craft.entries({
    or_lightswitchA: 1,
    or_lightswitchB: 1,
}) %}

Here's example code for the buildElementsQuery approach, the problem with this is that once you've converted to dbCommands you can't go back to a criteria model (which would be necessary to use the result with Craft's paginate tag for example).

public function myCustomCriteria($criteria, $lightswitchA_value, $lightswitchB_value)
{
    $dbCommand = craft()->elements->buildElementsQuery($criteria);

    $params = array(
        ':lightswitchA_value' => $lightswitchA_value,
        ':lightswitchB_value' => $lightswitchB_value,
    );

    $conditions = array('or',
        'content.field_lightswitchA=:lightswitchA_value',
        'content.field_lightswitchB=:lightswitchB_value',
    );

    $dbCommand->andWhere($conditions, $params);

    $results = $dbCommand->queryAll();

    return EntryModel::populateModels($results);
}