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.

Query builder joins error

Hello All,

I am trying to get used to using query builder, I have the following query:

$vehicles = $this->modelsManager->createBuilder() ->from('Vehicles') ->join('VehicleTypes', 'Vehicles.vehicle_typeid = VehicleTypes.id') ->orderBy('Vehicles.default_vehicle') ->getQuery() ->execute();

The first question I have is, whether I should be using the table names or the generated model names in the query. (ie. table name is vehicles and the model is Vehicles). Secondly I get the error Unknown model or alias 'VehicleTypes' do you know why? I referred to docs but couldn't find what I am missing from the code. The final question I have is relating to query performance, what is the best way to execute database queries within Phalcon to ensure best performance for the application?



84.1k
Accepted
answer
edited Apr '18

Here are two simple examples of Query Builder and joins.

public function getAll()
{
    $lang = $this->getDI()->getSession()->language ?? $this->getDI()->getConfig()->site->defaultLanguage;
    $cacheFile = 'products-all-'. $lang;
    $builder = $this->modelsManager->createBuilder();
    $builder->columns([
        'main.id',

        'main18.title',
        'main18.slug',
        'main18.volume',

        'upload.id AS uploadId',
        'upload.filename AS uploadFilename',
        'upload.ver AS uploadVersion',
    ]);
    $builder->from(['main' => 'Models\Products']);

    $builder->leftJoin('Models\ProductsI18n', 'main18.foreign_key = main.id', 'main18');
    $builder->leftJoin('Models\Uploads', 'upload.foreign_key = main.id AND upload.section = "productListImage" AND upload.is_active = 1 AND upload.is_default = 1', 'upload');

    $builder->where('main18.is_active = 1');
    $builder->andWhere('main18.lang = :lang:', ['lang' => $lang]);

    $builder->orderBy('main.ord ASC');
    return $builder->getQuery()->cache(['key' => $cacheFile])->execute();
}

public function getOne($slug)
{
    $lang = $this->getDI()->getSession()->language ?? $this->getDI()->getConfig()->site->defaultLanguage;
    $cacheFile = 'products-one-'. md5($slug . $lang);
    return $this->modelsManager->createBuilder()
        ->columns([
            'main.id',
            'main.ord',

            'main18.title',
            'main18.volume',
            'main18.content',
            'main18.use_guide AS useGuide',

            'upload.id AS uploadId',
            'upload.filename AS uploadFilename',
            'upload.ver AS uploadVersion',
        ])
        ->from(['main' => 'Models\Products'])

        ->leftJoin('Models\ProductsI18n', 'main18.foreign_key = main.id', 'main18')
        ->leftJoin('Models\Uploads', 'upload.foreign_key = main.id AND upload.section = "productDetailImage" AND upload.is_active = 1 AND upload.is_default = 1', 'upload')

        ->where('main18.is_active = 1')
        ->andWhere('main18.lang = :lang:', ['lang' => $lang])
        ->andWhere('main18.slug = :slug:', ['slug' => $slug])

        ->getQuery()->cache(['key' => $cacheFile])->getSingleResult();
}

About your questions:

Secondly I get the error Unknown model or alias 'VehicleTypes' - You have to use the full model with namespace like in my example above.

Performance - For presentation and list pages always specify only the columns you are going to use. If you do not specify columns or use alias.* whole model objects will be returned which will slow performance.

Security - Be it QueryBuilder or Simple PDO queries - ALWAYS use parameter binding to avoid sql injection :)



3.1k

As an extension to this question how can I check if the returned query is empty?

Thanks

edited Apr '18
    // Multiple results when using ->execute() in the Builder
    $result = (new \Models\Products)->getItems('some-non-existing-value');
    var_dump($result->count()); // int(0)

    // Single result when using ->getSingleResult()
    $result = (new \Models\Products)->getOne('some-non-existing-value');
    var_dump($result); // bool(false)