3

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.

  • 1
    I’m pretty sure you can’t do that natively use the field handle as an attribute (I might be wrong) but you could try it with the search parameter? – Josh Angell Apr 12 '16 at 10:31
  • @JoshAngell Thanks – as it happens, carlcs' answer solved this one for me nicely, but I'd love to see a way to do it using search, too, if you ever get the chance to type it out :) – Mats Mikkel Rummelhoff Apr 13 '16 at 2:28
4

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);
}
| improve this answer | |
  • carlcs, you're a champ. I've yet to test this (will do really soon), but A+ answer – thanks a lot! – Mats Mikkel Rummelhoff Apr 12 '16 at 11:05
  • I do actually need to paginate the results, btw – so I'll be going with the event approach. – Mats Mikkel Rummelhoff Apr 12 '16 at 11:07
  • I had to test if the two attributes were actually set for the $criteria object, but other than that, this worked like a charm. I did a minor edit to your answer to reflect what actually worked for me in the end. Thanks again! – Mats Mikkel Rummelhoff Apr 12 '16 at 23:18

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.