We have moved our forum to GitHub Discussions. For questions about Phalcon v3/v4/v5 you can visit here and for Phalcon v6 here.
  1. Home
  2. ORM

Can't specify AND/OR with inWhere method

How can I build the following query with the builder:

SELECT * FROM Post WHERE (Post.id IN (X) OR Post.element IN (Y));

Where (X) and (Y) would be arbitrary arrays of ids/elements

If I do the following:

$builder->from('Post') ->inWhere('id', X) ->inWhere('element', Y);

The inWhere method always seems to assume that the condition should be appended with an AND operator. Is there an easy way to create this query with the builder, or is it only possible by generating my own query string?

Any help would be appreciated. Thanks!



6.2k

Hm, hm with:

        $data = $this->modelsManager->createBuilder()
            ->columns('a.*')
            ->addFrom('Post ', 'a')
            ->inWhere('a.id  ' , $x_array)
            ->inWhere('a.element  ', $y_array)
            ->getQuery()
            ->execute()
            ->toArray();

we get 

WHERE (Post.id IN (X) **AND** Post.element IN (Y));

to get OR only I can tink of

        $data1 = $this->modelsManager->createBuilder()
            ->columns('a.*')
            ->addFrom('Post ', 'a')
            ->inWhere('a.id  ' , $x_array)
            ->getQuery()
            ->execute()
            ->toArray();

         $data2 = $this->modelsManager->createBuilder()
            ->columns('a.*')
            ->addFrom('Post ', 'a')
            ->inWhere('a.element  ', $y_array)
            ->getQuery()
            ->execute()
            ->toArray();

and to do array_merge.



12.2k
edited Jul '14

You can use orWhere as specified here https://docs.phalcon.io/en/latest/api/Phalcon_Mvc_Model_Criteria.html

alternatively you can use $parameters such as the following taken from the docs

 // Query robots binding parameters with string placeholders
$conditions = "name = :name: OR type = :type:";

//Parameters whose keys are the same as placeholders
$parameters = array(
"name" => "Robotina",
"type" => "maid"
 );

//Perform the query
$robots = Robots::find(array(
$conditions,
"bind" => $parameters
 ));

// Query robots binding parameters with integer placeholders
$conditions = "name = ?1 AND type = ?2";
$parameters = array(1 => "Robotina", 2 => "maid");
$robots     = Robots::find(array(
$conditions,
"bind" => $parameters
));

// Query robots binding parameters with both string and integer placeholders
$conditions = "name = :name: AND type = ?1";

//Parameters whose keys are the same as placeholders
$parameters = array(
"name" => "Robotina",
1 => "maid"
);

//Perform the query
$robots = Robots::find(array(
$conditions,
"bind" => $parameters
));


3.0k

Thanks for the suggestions.

I suppose i can do an array merge but that defeats the purpose of having a single query. Would this be considered a bug / missing feature? Is there a place to report it?

@TommyBs, I already know those capablities of the builder api, I'm talking specifically about the IN condition in sql, a normal Or [equality] is straight forward.

The only other way I can think of is to use the

$builder->orWhere('element IN (:p1:, :p2:, .. :pN:)', array('p1' => Y[0], 'p2' => Y[1] ...))

Where we would have a helper method that takes an array and generates the placeholder part, ie. (:p1:, :p2: ...) and produces a corresponding key => value array.

This approach would work but it is obviously not ideal, it would be nice to be able to specify the and/or operator for inWhere() or maybe as andInWhere / orInWhere



12.2k
edited Jul '14

Apologies, I misread your initial question

I use something such as 

    if(count($this->items) > 0 ){
        $in_query = implode(",", array_fill(0,count($this->items), '?'));
    }else{
        $in_query = ""; 
    }

to generate placeholders for something similar. But I'll have a think of any other solutions as well