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屋！