Solved thread

This post is marked as solved. If you think the information contained on this thread must be part of the official documentation, please contribute submitting a pull request to its repository.

Most efficent way doing join selects

Hi! Im making app that has database with many connections (many-to-many) I guess. it has a lots of smth like this tables article (id, name) tags (id, name) article_to_tags (article_id, tag_id)

I made one with orm but Im worried about speed. I need the most efficent way of converting this sql to phalcon

$sql = "SELECT FROM countries INNER JOIN movies_to_countries ON movies_to_countries.country_iso31661=countries.iso31661 WHERE movies_to_countries.movie_id = 277"

edited Sep '15

Phalcon will convert every ORM query to regular SQL, so there is no magic there. The only thing that can speed up selects is caching.

// services.php
$di->set('modelsCache', function () {

    // Cache data for one day by default
    $frontCache = new FrontendData(
            "lifetime" => 86400

    // Memcached connection settings
    $cache = new BackendMemcache(
            "host" => "localhost",
            "port" => "11211"

    return $cache;

If you have setup your Models properly, you can use either related models:

$movieToCountry     = Moviestocountries::findFirst(277);
$country = $movieToCountry->getCountries();

or PHQL:

$phql = "SELECT c.* FROM Countries c INNER JOIN Moviestocountries m ON m.country_iso31661=c.iso31661 WHERE m.movieid = :movieid:";
$query = $this->modelsManager->createQuery($phql);
$country = $query->execute(
        'movieid' => 277

Also, using integers as foreign keys is preferable to char/varchar (iso31661)