Cphalcon: Combining and and or operators in Phalcon\Mvc\Model\Criteria

[tflori]

I've opened a question in stackoverflow - nobody seems to have an answer. So there is a feature request.

It's currently not possible to generate complex where conditions without writing an phql statement. In other programs this will be made by allOf(condition1, condition2, anyOf(condition3, condition4)) - sieve filtering for example.

It would be nice to have a function that opens a parenthesis where you can put in all your conditions.

Suggested syntax:

<?php

$query = Table::query();
$query->where('status = :status:', ['status' => 'A']);
/** 
 * the nest object is similar to the query object. these functions are included:
 * public Phalcon\Mvc\Model\Nest where (unknown $conditions, [unknown $bindParams], [unknown $bindTypes])
 * public Phalcon\Mvc\Model\Nest andWhere (unknown $conditions, [unknown $bindParams], [unknown $bindTypes])
 * public Phalcon\Mvc\Model\Nest orWhere (unknown $conditions, [unknown $bindParams], [unknown $bindTypes])
 * public Phalcon\Mvc\Model\Nest andNest ()
 * public Phalcon\Mvc\Model\Nest orNest ()
 **/
$nest1 = $query->andNest();
$nest1->where('title LIKE :title:', ['title' => 'combining%']);
$nest1->orWhere('title LIKE :title:', ['title' => 'phalcon%']);
$nest2 = $nest1->orNest();
$nest2->where('title LIKE :title:', ['title' => 'criteria%']);
$nest2->andWhere('title LIKE :title:', ['title' => '%phalcon']);

$query->execute();
/**
 * executed query:
 * SELECT * 
 * FROM table
 * WHERE
 *   status = 'A' AND
 *   (
 *     title LIKE 'combining%' OR
 *     title LIKE 'phalcon%' OR
 *     (
 *       title LIKE 'criteria%' AND
 *       title LIKE '%phalcon'
 *     )
 *   )
 */

[williamokano]

This cannot be done using criteria addWhere, andWhere or orWhere?

https://api.phalconphp.com/class/Phalcon/Mvc/Model/Criteria.html

[tflori]

Of course it can be made with an andWhere('(condition1 or condition2 or (condition3 and condition4))' but this complexity can be outsourced to a class.

This is a feature request. Yes I can write a class by my own but than others still have the same issue. Also I have to extend Criteria and Model - because Model::query() have to create a MyCriteria object and Criteria have to travers all where conditions if they are from type nest.

btw. addWhere is deprecated.

That was the long answer. The short one is: no, not that simple.

[KorsaR-ZN]

@tflori I've been working on this issue. In the near future will be PR.
Unfortunately it is impossible to do, as you have suggested without violating backward compatibility.
So there will be the following syntax:

$query = Table::query();
$query->where('status = :status:', ['status' => 'A']);

$group1 = $query->whereGroup();
$group1->where('title LIKE :title:', ['title' => 'combining%']);
$group1->orWhere('title LIKE :title:', ['title' => 'phalcon%']);

$group2 = $query->whereGroup();
$group2->where('title LIKE :title:', ['title' => 'criteria%']);
$group2->andWhere('title LIKE :title:', ['title' => '%phalcon']);

// Append in group1
$group1->orWhere(group2);

// Append in main query
$query->andWhere($group1);

$query->execute();

/**
 * executed query:
 * SELECT * 
 * FROM table
 * WHERE
 *   status = 'A' AND
 *   (
 *     title LIKE 'combining%' OR
 *     title LIKE 'phalcon%' OR
 *     (
 *       title LIKE 'criteria%' AND
 *       title LIKE '%phalcon'
 *     )
 *   )
 */

[tflori]

sounds great. looking forward to have it in my phalcon :)

[williamokano]

:+1:

[Green-Cat]

:+1: I've had to use complex generated PHQL before for custom filter based search because of the lack of this in the query builder.

[nejtr0n]

+1

[dzubchik]

@KorsaR-ZN , what feature status?

[stale[bot]]

Thank you for contributing to this issue. As it has been 90 days since the last activity, we are automatically closing the issue. This is often because the request was already solved in some way and it just wasn't updated or it's no longer applicable. If that's not the case, please feel free to either reopen this issue or open a new one. We will be more than happy to look at it again! You can read more here: https://blog.phalconphp.com/post/github-closing-old-issues