Solved thread

This post is marked as solved. If you think the information contained on this thread must be part of the official documentation, please contribute submitting a pull request to its repository.

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



17.3k

Try with http://docs.phalconphp.com/es/latest/api/Phalcon%5Mvc%5Model%5Criteria.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");


1.3k
Accepted
answer