PHQL"WHERE xxx IN ()"只能得到1个数据 [英] PHQL "WHERE xxx IN ()" can get only 1 data

问题描述

我正在使用 Phalcon 创建一个 RESTful API.
我想通过 ID 从商店"表中获取一些数据.
PHP代码:

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;
}

测试:

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

但是我只能得到一个id为1的数据.我也试过了:

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

这将返回一个 id 为 2 的数据.

This returns one data whose id is 2.

为什么我无法获取多个数据?我的日志说 $ids_str = "1,2"，所以我认为 phql 查询可能是正确的...

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

推荐答案

因为同时使用 PDO 和 PHQL 的例子很少 此处 和此处 适合您的示例，在 Phalcon queryBuilder 机制中还有另一种可能的方法:

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 方法只能通过 queryBuilder 访问，它的工作方式与上述 PDO 示例 IN (?, ?, ?) 完全相同.但是您不需要手动实现它.

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.

您也可以跳过创建 PHQL 的一部分，直接创建 SQL 查询，但需要自己进行验证.

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));

这篇关于PHQL"WHERE xxx IN ()"只能得到1个数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！