PHQL“WHERE xxx IN（）”只能获得1个数据

Question

I'm creating a RESTful API with Phalcon.
I want to get some data from "Shop" table by IDs.
PHP code:

$app->post('/api/shops/search', function () use ($app) {
    $ids_shop = $app->request->getJsonRawBody();
    $ids = array();
    foreach($ids_shop as $id){
            $ids[] = $id->id_shop;
    }
    $ids_str = implode(",", $ids);
    $shops = getShopsData($ids_str, $app);

    return $shops;
});

function getShopsData($ids_shop, $app) {
    $phql = "SELECT * FROM Shops WHERE Shops.id IN ( :ids: )";
    $shops = $app->modelsManager->executeQuery($phql, array(
        'ids' => $ids_shop
    ));
    return $shops;
}

Test:

curl -i -X POST -d '[{"id_shop":1},{"id_shop":2}]' http://localhost/sample/api/shops/search

However I can get only one data whose id is 1. And I also tried it:

curl -i -X POST -d '[{"id_shop":2},{"id_shop":1}]' http://localhost/sample/api/shops/search

This returns one data whose id is 2.

Why cannot I get multiple data? My log says $ids_str = "1,2", so I think phql query might be correct...

Answer 1

As there are few examples working with both PDO and PHQL here and here that suites to your example, there is one more approach possible in Phalcon queryBuilder mechanism:

$builder = $this->modelsManager->createBuilder();
$builder->addFrom('Application\Entities\KeywordsTrafficReal', 'tr')
        ->leftJoin('Application\Entities\Keywords', 'kw.id = tr.keyword_id', 'kw')
        ->inWhere('keyword_id', self::$keywords) // <<< it is an array!
        ->betweenWhere('traffic_date', self::$daterange['from'], self::$daterange['to']);

inWhere method is reachable only via queryBuilder as far as i know and it works exactly the same way as mentioned PDO examples IN (?, ?, ?). But you are not in need of implementing it by hand.

You can also skip part of creating PHQL and drive directly to create SQL query, but on cost of making own validations.

$realModel = new KeywordsTrafficReal();

$sql = sprintf('SELECT * '
            .  'FROM keywords_traffic_real tr '
            .  'LEFT JOIN keywords kw '
            .  'ON kw.id = tr.keyword_id '
            .  'WHERE tr.keyword_id IN(%s) '
            .  "AND traffic_date BETWEEN '%s' AND '%s' ",
        join(',', self::$keywords), self::$daterange['from'], self::$daterange['to']);

$results = new \Phalcon\Mvc\Model\Resultset\Simple(null, $realModel, $realModel->getReadConnection()->query($sql));