1

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 }
            ]}
        ]
    })
%}

2 Answers 2

2

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;
    }
);
1

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)
) %}
2
  • Thanks for your response. Glad to hear I'm not the only one who thinks this notation is convoluted. I can't use the combining solution as I need to pass the query to a pager function. I'm posting the solution I ended up using below.
    – bartpulse
    Oct 26, 2023 at 13:55
  • 1
    Ok cool. Curious why you can't use Craft's built-in pagination functionality? You don't need to call .all() on each query - you could use .ids() instead to build up an array of ids before then creating the final query for those ids, which then gets paginated. Oct 26, 2023 at 17:11

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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