Problem with raw queries.

Question

Problem with raw queries.

KomorowskiMaciej May '15

Created May '15	Last Reply May '15	Replies 1	Views 679	Votes 0

KomorowskiMaciej 750

May '15

Hi, I'm just starting with phalcon and i have a little trouble with queries. I want to use raw query to create temp table, insert some info to it and select items form another table using this temp like pattern to order. So, how can i do it to be compatible with phalcon methods?

When i want to test Phalcon\Mvc\Model\Query

    $query = new Query("SELECT * FROM Dishes", $this->getDI());
    return $query->execute();

it returns object(Phalcon\Mvc\Model\Resultset\Simple) and it isn't contains a results.

Here is my attempt to resolve:

    $this->modelsMenager->executeQuery("CREATE TEMPORATY TABLE pattern (ingredient INT);");
    $this->modelsMenager->executeQuery("INSERT INTO pattern (ingredient) VALUES (1),(2),(3);");

    $query = $this->modelsManager->createQuery(
        "SELECT dishes.name FROM dishes ORDER BY(
            SELECT COUNT(1) FROM features, pattern
            WHERE features.dishes_id = dishes.id AND features.ingredient = pattern.ingredient) DESC;"
    );  
    $result =  $query->execute();

I notice that in raw sql these queries works correctly.

Calin Rada · Answer 1 · 2015-05-20T04:20:26-07:00

try this:

        $sql = "SELECT dishes.name FROM dishes ORDER BY( SELECT COUNT(1) FROM features, pattern WHERE features.dishes_id = dishes.id AND features.ingredient = pattern.ingredient) DESC";

        $model   = new YourModel(); // new Dishes() ?
        $results = new \Phalcon\Mvc\Model\Resultset\Simple(null, $model, $model->getReadConnection()->query($sql));