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

Question

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

Thiago Yoithi Nov '15

Created Nov '15	Last Reply Dec '16	Replies 3	Views 1753	Votes 0

Thiago Yoithi 3.6k

Nov '15

I have this relationship in my MySQL database:

example

Now i want to access the "is_main" attribute from the middle table "product_image", 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 "is_main" attribute when I have a product instance. Can I access the "is_main" 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!

Lajos Bencz
77.7k

Accepted
answer

edited Nov '15
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.phalcon.io/en/latest/reference/models.html#defining-relationships

Thiago Yoithi · Answer 1 · 2015-11-09T05:47:42-07:00

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

Wojciech Ślawski · Answer 2 · 2015-11-10T03:00:55-07:00

Wojciech Ślawski
145.0k

Nov '15

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

Mathieu Robardey · Answer 3 · 2016-12-27T05:22:08-07:00

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
                    ]
                ]
            ]
        );
    }
}