Problem with raw queries.

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.



33.9k

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