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 = 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!

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



65.9k
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, isactive, ord, createdat

seriesi18n with columns foreignkey, 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.phalconphp.com/en/latest/api/Phalcon_Mvc_Model_Query_Builder.html