Query builder joins error

Question

Query builder joins error

drc83 Apr '18

Created Apr '18	Last Reply Apr '18	Replies 2	Views 268	Votes 0

drc83 5.7k

Apr '18

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?

Nikolay Mihaylov
93.7k

Accepted
answer

edited Apr '18
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 :)

drc83 · Answer 1 · 2018-04-10T11:04:37-07:00

drc83
5.7k

Apr '18

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

Thanks

Nikolay Mihaylov · Answer 2 · 2018-04-10T23:58:32-07:00

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