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.

Model Show Related Records based on multiple parameters

Hi guys,

I am developing a sample app and would like your input on something. I currently have 2 models, Products and ProductSpecialPrices, products has a hasMany relationship to ProductSpecialPrices and ProductSpecialPrices has a belongsTo relationship to Products.

ProductSpecialPrices' fields include productid (from Products), special-price and userId. Each userId can have many ProductSpecialPrices records, one for each productId. So here comes the question:

When querying the Products model, I also want to get the related records based on the userId I specify. I read somewhere that I can specify parameters to the relationship, but I cannot find documentation for this. On plain SQL, I would need to do something like this:

SELECT * FROM Products
INNER JOIN ProductSpecialPrices ON ProductSpecialPrices.productId =
WHERE userId = XX

The resulting Products would be presented in a simple view.

P.S. This is a sample app to help me familiarize with more advanced Phalcon techniques, so a simple example will help...


Use query builder ?

That was my original approach, I just want to know if there is a better way to do this.

Nope, query builder is best :)

edited Aug '16

Here is a sample join query using the Query Builder.

$lang = 'bg';
data = $this->modelsManager->createBuilder()
    ->from(['main' => 'Models\Series'])
    ->leftJoin('Models\SeriesI18n', 'main18.foreign_key =', 'main18')
    ->where('main.is_active = 1')
    ->andWhere('main18.lang = :lang:', ['lang' => $lang])
    ->orderBy('main.ord ASC')

Important: please note that query builder is using Models and not table names.

The above query is joining the following tables:

series with columns: id, is_active, ord, created_at

series_i18n with columns foreign_key, lang, title etc... This table I use to store all my translations.

Also note: Using model relations like hasMany and others is not the same as join, but will make additional query for every record in a loop. So as Wojciech said, stick to QueryBuilder :)

You can read more about query builder in the docs: