How to first create Criteria and then add it to the query

[roomcays]

I have stumbled upon a situation where I'd like to first "build" a Criteria object and then apply it to the query using, for example, andWhere() method.

I can not (or I don't know how to do it) chain criterias as they're set in certain conditions and grouped in several ANDs and ORs.
What I normally do with, for example arrays, I create an empty array, then I fill it with values (while looping and processing conditions). Optionally I do check if array is empty and then apply it (if it is not empty) or not (if it is).

So I'm thinking the same way about Criterias and I can not "create" an empty Criteria object without breaking the query (in a weird way).

Example code:

$criteria = new Criteria();

foreach ($conditions_1 as $condition_1) {
    foreach ($conditions_2 as $condition_2) {
        if ($condition_1 and $condition_2) {
            $criteria->or(field('foo')->eq(true)->and(field('bar')->eq(true));
        } else if ($condition_1) {
            $criteria->or(field('foo')->eq(true));
        } else {
            // Do nothing (to explicitly emphasize that in some cases built Criteria may be empty at the end
        }
    }
}

if ($criteria) {
    $query->andWhere($criteria);
}

Can anybody please point me a way to go? Maybe there is an another approach for such situation?

[shadowhand]

I don't really follow this example. Typically the way to do this would be:

$select = $factory->select(...);

foreach (...) {
    if ($condition) {
        $select->andWhere(...);
    } else {
        $select->orWhere(...);
    }
}

That being said, you could absolutely create your own CriteriaBuilder class that has and() and or() methods and then:

if ($builder->hasCriteria()) {
    $query->andWhere($builder->buildCriteria());
}

[roomcays]

:) It is hard for me to explain this to other person even in Polish (my native language), and gets even harder when it comes to English.

It is about creating a group of conditions that are later applied to the query (or not if they're "empty") wrapped in single AND or OR operator.

Take a look at another example (hopefully better one). Imagine a situation when query must filter out records that have different model than this provided by user (array $models). Every $model can be represented by:

1. model name such as Transit
2. combination of brand and model, such as Ford|Transit
3. null that means "no model at all" and is valid query filter

Now, every of these combinations requires to be added to the query differently in the foreach loop:

// ... this query has already applied some contitions
$query->andWhere(field('user')->eq('Fred'));

// Now it is time for the "criteria container" (name is temporary, relates
// to using arrays in similar situations)
$criteria = new CriteriaContainer();

foreach ($models as $model) {
    if (false !== strpos($model, '|')) {
        // Here models are defined in "MODEL|BRAND" format, i.e. "Ford|Transit" or "Toyota|Celica"
        [$brand, $model] = explode('|', $model);
        // In this case model's name is strictly bound with brand, so the `AND` is required.
        // But while model can be any string the brand name must be checked:
        if (\App\Model\Brand::isValid($brand)) {
            $criteria->or(field('Brand')->eq($brand)->and(field('Model')->eq($model)));
        } // if brand isn't valid criteria is not added
    } elseif ($model === null) {
        // Allow also records that do not have *model* set at all
        $criteria->or(field('Model')->isNull());
    } else {
        // Treat all other strings as they're model names. This will give in result records
        // with models (like `Celica`, `Transit`) but will not care about the brand
        // so when there will be `Audi|Celica` or `BMW|Transit` they will alse be selected
        $criteria->or(field('Model')->eq($model));
    }
}

// Let's check if container contains anything. Due to 'brand' check there are
// possible situations when after looping no criteria has been added, thus
// container is "empty"

if (!$criteria->isEmpty()) {
    // To make example more complicated and less sensible, use `OR`
    // operator for whole group of conditions
    $query->orWhere(group($criteria));
}

Some other PHP SQL DBALs I've seen and worked with would apply following schema of dealing with that situation (similar, but not the same):

$query->openOr();
foreach (...) {
    if ($condition) {
        $query->andWhere(...);
    } else {
        $query->orWhere(...);
    }
}
$query->closeOr();

I will now try to follow your idea of creating my own CriteriaBuilder. I appreciate your quick reply, thanks!

---

EDIT: an example of SQL query created:

SELECT * FROM vehicles
WHERE
    `user` = "Fred" 
    OR ( -- AND is also an option here
        (`Brand` = "Ford" AND `Model` = "Transit")
        OR `Model` IS NULL
        OR `Model` = "Celica"
        OR (`Brand` = "BMW" AND `Model` = "X5")
        OR `Model` = "Wrangler"
    )

[roomcays]

I have twisted my mind for couple of days and applied following solution:

foreach ($models as $model) {
    if (false !== strpos((string) $model, '|')) {
        // Here models are defined in "MODEL|BRAND" format, i.e. "Ford|Transit" or "Toyota|Celica"
        [$brand, $model] = explode('|', $model);
        // In this case model's name is strictly bound with brand, so the `AND` is required.
        // But while model can be any string the brand name must be checked:
        if (\App\Model\Brand::isValid($brand)) {
            $condition = field('Model')->eq($model)->and(field('Brand')->eq($brand));
        } // if brand isn't valid criteria is not added
    } elseif ($model === null) {
        // Allow also records that do not have *model* set at all
        $condition = field('Model')->isNull();
    } else {
        // Treat all other strings as they're model names. This will give in result records
        // with models (like `Celica`, `Transit`) but will not care about the brand
        // so when there will be `Audi|Celica` or `BMW|Transit` they will also be selected
        $condition = field('Model')->eq($model);
    }

    // The tricky part: if criteria's object already exists (from previous loops), add current condition using
    // OR operator, otherwise create initial criteria object (any potential future condition will be added to
    // this one):
    if (isset($model_criteria)) {
        $model_criteria = $model_criteria->or($condition);
    } else {
        $model_criteria = $condition;
    }
}

// Finally check if the loop has created the criteria object at all (remember the brand validation procedure?).
// If so - add it to the query as group (wrap it in braces).
if (isset($model_criteria)) {
    $query->andWhere(group($model_criteria));
}

While this works, it is, in my opinion, rather a workaround than a solution. I must now continue development of the project I use latitude for. I let you @shadowhand decide if this is something worth developing (I might try to contribute) or no.

Have a good/better 2022! 🎆 🍾 👯

[shadowhand]

@roomcays that seems like a reasonably good solution. A future version of Latitude might include a criteria builder that will mimic this functionality. Thanks!