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. Database

Problem with InWhere and where

Hi all,

My purpose is execure a query in Model: 

select * from products where type = 1 and id IN(1,2,3,4,5,6);

I tried some options as below:

Option 1: 

$products = $this->modelsManager->createBuilder()
    ->from('Products')
    ->where('type = :type:')
    ->inWhere('id = :id:')
    ->getQuery()
    ->execute(array('type' => $type, 'id' => $arrayIDs));

==> But result is: 

select * from products id IN(1,2,3,4,5,6); // Not type = 1

Option 2: 

$products = $this->modelsManager->createBuilder()
    ->from('Products')
    ->inWhere('id = :id:')
    ->andWhere('type = :type:')
    ->getQuery()
    ->execute(array('id' => $arrayIDs, 'type' => $type));

==> But result is: 

select * from products where type = 1; // Not id IN (1,2,3,4,5,6)

:( :(

I also tried using placeholder with a phql as below:

$sql = "SELECT * FROM Products where type = :type: and id IN (:IdList:)";
$manager->executeQuery($sql,
    array(
        'type'     => $type,
        'idList' => $arrayIDs
    )
);

==> But result is: 

select * from products where type = 1; // Not id IN (1,2,3,4,5,6)

Could you tell me what is the problem in this case? Maybe, the Phalcon is not support both where and inwhere included in a query????

Thanks



33.8k

Try with https://docs.phalcon.io/es/latest/api/Phalcon_Mvc_Model_Criteria.html :

// Don't sure if this will work.
$products = Product::query()
    ->where('type = :type:')
    ->inWhere('id', $arrayIDs)
    ->bind(['type' => $type])
    ->execute();

// But I'm sure this one will. Maybe you will need to format '$arrayIDs' to a string with the parenthesis, isn't tested.
$products = Product::query()
    ->where('type = :type:')
    ->andWhere('id IN :id:')
    ->bind(['type' => $type, 'id' => $arrayIDs])
    ->execute();

// Also I think you could do it with this. Same advice to '$arrayIDs'.
$products = Product::find("type = $type AND id IN $arrayIDs");


3.0k
Accepted
answer
edited Jan '15