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.

Get a column from the intermediate table in a many-to-many relationship

I have this relationship in my MySQL database:

example

Now i want to access the "ismain" attribute from the middle table "productimage", for example:

example1

I can't do the above example because is_main is not part of the "image" table.

I tried doing something like this too:

But it not worked. In short, I want to access the "ismain" attribute when I have a product instance. Can I access the "ismain" attribute and the respective image table using only one database request?

My model:

I think I don't need to post my product and image models.

Thanks!



38.6k
Accepted
answer
edited Nov '15

They key is to set up the relations on every model:

class Product extends PhModel
{
    public function initialize() {
        $this->hasMany('id','ProductImage','product_id',['alias'=>'product_images']);
        $this->hasManyToMany('id','ProductImage','product_id','image_id','Image','id',['alias'=>'images']);
    }
}
class Image extends PhModel
{
    public function initialize() {
        $this->hasMany('id','ProductImage','image_id',['alias'=>'product_images']);
        $this->hasManyToMany('id','ProductImage','image_id','product_id','Product','id',['alias'=>'products']);
    }
}
// This isnt really necessary
class ProductImage extends PhModel
{
    public function initialize() {
        $this->belongsTo('product_id','Product','id',['alias'=>'products']);
        $this->belongsTo('image_id','Image','id',['alias'=>'images']);
    }
}

Now you can access the intermediate model:

$product = Product::findFirst();
foreach($product->product_images as $i) {
    var_dump($i->toArray());
    // $i->is_main
}
foreach($product->images as $i) {
    var_dump($i->toArray());
    // $i->path
}

https://docs.phalconphp.com/en/latest/reference/models.html#defining-relationships

edited Nov '15

Hahaha, I did set $this->hasManyToMany('id','ProductImage','productid','imageid','Image','id',['alias'=>'images']); but didn't set $this->hasMany('id','ProductImage','productid',['alias'=>'productimages']); So that is why my second example didn't work. Thank you very much!

Better to use queryBuilder and joins..... instead of doing foreach and so many selects.

This can be done by declaring columns in options :

class Site extends Model
{
    public function initialize()
    {
        $this->hasManyToMany(
            'id',
            'UserSite',
            'site_id', 'user_id',
            'User',
            'id',
            [
                'alias' => 'users',
                'params' => [
                    'columns' => [
                        'User.id',
                        'User.name',
                        'User.email',
                        'UserSite.role' # <= add the join table column
                    ]
                ]
            ]
        );
    }
}