Cleaning up twig query with nested and/or where

Question

I have troubles understanding the nested twig query syntax. It works fine in PHP code but somehow when using twig I never seem to get it right.

This is my raw, dirty query which I want to clean up a bit:

{% set tempQuery   = craft.products()
    .type('product')
    .where('(field_onlyShowMainProduct_njwytbyx = 0 OR field_onlyShowMainProduct_njwytbyx IS 
    NULL) OR (field_onlyShowMainProduct_njwytbyx = 1 AND 
    field_f_product_isMainProduct_ftgnylbr = 1)')
%}

I managed to get it cleaner in PHP:

Product::find()
    ->type('product')
    ->where([
            'or',
                ['field_onlyShowMainProduct_njwytbyx' => [0, null]],
                ['and',
                    ['field_onlyShowMainProduct_njwytbyx' => 1],
                    ['field_isMainProduct_ftgnylbr' => 1]
                ]
            ])

But in twig I can't get it to work. I just get error after error. Feels like i'm missing some basic understanding of nesting and twig formatting arrays. When to use "," or ":", "{" or "[". The examples in the Craft docs are also very simple queries. Any ideas? Suggestions? Fixes?

{% set tempQuery = craft.products()
    .type('product')
    .where({
        or: [
            { 'field_onlyShowMainProduct_njwytbyx': [0, null] },
            { and: [
                { 'field_onlyShowMainProduct_njwytbyx': 1 },
                { 'field__isMainProduct_ftgnylbr': 1 }
            ]}
        ]
    })
%}

Answer 1

I ended up using a ProductQuery Behavior. This is quite handy as the same query is used in different pages. My final solution:

Twig

{% set tempQuery = craft.products()
    .type('product')
    .whereParentProduct()
%}

Behavior Class

<?php

namespace module\base\behaviors;

use craft\commerce\elements\db\ProductQuery;
use yii\base\Behavior;

/**
 * @property ProductQuery $owner
 */
class ParentProductQuery extends Behavior
{
    public function whereParentProduct()
    {
        return $this->owner->where([
            'or',
            ['field_onlyShowMainProduct_njwytbyx' => [0, null]],
            ['and',
                ['field_onlyShowMainProduct_njwytbyx' => 1],
                ['field_isMainProduct_ftgnylbr' => 1]
            ]
        ]);
    }
}

Adding the Behavior using an Event

Event::on(
    ProductQuery::class,
    ProductQuery::EVENT_DEFINE_BEHAVIORS,
    function (DefineBehaviorsEvent $event) {
        $event->behaviors['parentProductQuery'] = ParentProductQuery::class;
    }
);

Answer 2

Ordinarily I'm a fan of being as lean and efficient as possible, but it strikes me that this where clause makes your Twig quite hard to read - as you say, getting the all the square brackets and quote marks and commas in the right place is fiddly and error-prone. You might want to consider two alternative approaches, which would be less efficient but more readable:

{# ==================================
Approach 1: as you were, make a complicated-
looking query to get only what we need.
(I haven't tested this, sorry in advance if it's not quite right!)
===================================== #}

{% set tempQuery = craft.products()
    .type('product')
    .where([
        'or', [
            ['field_onlyShowMainProduct_njwytbyx', [0, null]],
            ['and', [
                ['field_onlyShowMainProduct_njwytbyx', 1],
                ['field_isMainProduct_ftgnylbr', 1]
            ]]
        ]
    ])
    .all()
%}

{# ==================================
Approach 2: make a pair of simpler
separate queries and then merge them
===================================== #}

{% set firstSet = craft.products()
    .type('product')
    .onlyShowMainProduct(false)
    .all()
%}
{% set secondSet = craft.products()
    .type('product')
    .onlyShowMainProduct(true)
    .isMainProduct(true)
    .all()
%}
{% set mergedResults = firstSet|merge(secondSet) %}

{# ==================================
Approach 3: make a single large query
and then filter the results
===================================== #}

{% set allProducts = craft.products().type('product').all() %}
{% set results = allProducts|filter(product =>
    not product.onlyShowMainProduct or
    (product.onlyShowMainProduct and product.isMainProduct)
) %}