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) articletotags (articleid, tagid)

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

$sql = "SELECT countries.name FROM countries INNER JOIN moviestocountries ON moviestocountries.countryiso31661=countries.iso31661 WHERE moviestocountries.movieid = 277"



35.7k
Accepted
answer
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(
        array(
            "lifetime" => 86400
        )
    );

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

    return $cache;
});

If you have setup your Models properly, you can use either related models: https://docs.phalconphp.com/en/latest/reference/models-cache.html#caching-resultsets

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

or PHQL: https://docs.phalconphp.com/en/latest/reference/models-cache.html#caching-phql-queries

$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(
    array(
        'movieid' => 277
    )
);

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