We have moved our forum to GitHub Discussions. For questions about Phalcon v3/v4/v5 you can visit here and for Phalcon v6 here.
  1. Home
  2. ORM

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 = Product.id
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...

Thanks!



145.0k

Use query builder ?



8.9k

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



145.0k

Nope, query builder is best :)



93.7k
Accepted
answer
edited Aug '16

Here is a sample join query using the Query Builder.

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

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: https://docs.phalcon.io/en/latest/api/Phalcon_Mvc_Model_Query_Builder.html



8.9k

Thanks @nikolay-mihaylov