Selecting join table with mant to many?


(Chris De Kok) #1

What is the best way to select (extra) values from the join table in a many to many relationship.


(Creocoder) #2

There is no best way now. Framework needs association table handling approach. I think this will be implemented later.

I think it can work like shown:




class User extends CActiveRecord

{

    ...

    public function relations()

    {

        return array(

            'products'=>array(self::MANY_MANY,'ShoppingCart(userID,productID)','association'=>'cart'),

        );

    }

    ...

}


class Products extends CActiveRecord

{

    ... //Products model logic

}


class ShoppingCart extends CActiveRecord

{

    ... //ShoppingCart model logic

}



Now let’s:




$user=User::model()->with('products')->findByPk(1);


foreach($user->products as $product)

{

    echo $product->title; //prints product title

    echo $product->cart->quantity; //this is additional field from ShoppingCart, prints product quantity in cart

    echo $product->cart->orderDate; //this is other additional field from ShoppingCart, prints ordering date of this product

}



As seen, ‘cart’ is virtual relation, that AR automatically create for Product model. It’s as i see association table additional fields handling.

With this approach we can even use ‘cart’ in ‘condition’ part like this:




$users=User::model()->with('products')->findAll('cart.quantity > 5');



As seen with this, AR MANY_MANY association will be very more flexibly and powerfully. Now there is some disadvantage of using AR MANY_MANY in real applications. In practice associating table without additional fields is very rare.

Qiang, what do you think about ‘association’ option approach?


(Chris De Kok) #3

:) This would be a nice option… but is there anyway to go around it (without just typing out the entire query?)


(Mh) #4

@mech7:

Here’s another way:

http://www.yiiframew…-the-link-table

@creocoder:

Nice approach. I agree this should work, as the connection table is used in the query anyway. So it’s merely a matter of where to map the results from the connection table to. It would be more consistent to use an ActiveRecord for it (in your case: ShoppingCart). That way you can also update/delete that record.

I think, it requires 2 parameters: the name of the ActiveRecord for the relation table (e.g. ShoppingCartRecord, as not always table name==record name), and the name of the property where it should be mapped to (e.g. cart) in a product.

So how about a relation like this:


// User.php

public function relations()

{

    return array(

        'products'=>array(self::MANY_MANY, 'Product', 'cart_table(user_id,product_id)',

            'association'=>array('cart','ShoppingCart')),        

    );

}



Or another approach: We could use a "special" HAS_ONE relation in Products for this:


// In Product.php:

public function relations() {

    return array(

        'carts'=>array(self::HAS_MANY, 'ShoppingCart', 'product_id'),


        // special relation, only used for a product in a specific cart

        'cart'=>array(self::HAS_ONE, 'ShoppingCart', 'product_id',/* more options for this relation, like 'select', ... */),

    );

}



Now we could define the relation in User like you suggested. The information where ShoppingCart should be mapped to is defined in the above HAS_ONE relation.


// User.php

public function relations()

{

    return array(

        'products'=>array(self::MANY_MANY,'Products', 'cart_table(user_id,product_id)','association'=>'cart'),

    );

}

Just brainstorming … more ideas?


(Creocoder) #5

Thanks. :rolleyes:

If we have additional fields in associative table, it’s in 99,9% have own Model. So 1 parameter needed. AR can call ShoppingCart::tableName() to see what table name using. In anyway current syntax ‘cart_table(user_id,product_id)’ is not effective in practice. I suggest that it must be ‘ModelClass(user_id,product_id)’, for that example ‘ShoppingCart(user_id,product_id)’ always.

P.S. If you need this feature, please vote it at http://code.google.com/p/yii/issues/detail?id=1117


(Mh) #6

How would you know the name of the ActiveRecord class? In your case tablename==class name. That’s not always the case. Like you said now it’s ‘cart_table(user_id,product_id)’ and that’s fine, because it doesn’t require to always create a AR for the connecting table.

It also would break bc if we change that to be the AR class name.


(Creocoder) #7

No. We have CActiveRecord::tableName() method for using when table_name != class name.

No, it’s full BC. Framework should check class file and if this is not found, it use part of ‘[b]cart_table/b’ (i bold part) as table_name.


(Creocoder) #8

I think and practice show, that associative table without AR class is particular case. If associative table have only FK’s, yes it’s can not have own Model class. But if we use associative tables with additional fields (about handling it that topic) it use own Model always. So, now framework can be used for resolving particular cases only. I think we can resolve tasks more widely.


(Mh) #9

I see your point.

Actually i don’t really like the potential double meaning of ‘ShoppingCart(user_id,product_id)’. With your suggestion, ShoppingCart could either be a table or a AR class name, right? Things should be unabmiguous. How about using different notations for both:

‘cart_table(user_id,product_id)’ -> current implementation (table name)

‘ShoppingCart[user_id,product_id]’ -> Use class ShoppingCart to find details for connecting table

Then your ‘associative’ feature would require the latter format.


(Creocoder) #10

Yes.

Please, explain this more.

Yes, it nice too. So framework always know what we mean.


(Mh) #11

It should be clear, what exactly ‘ShoppingCart(…)’ relates to. I don’t like it to have a double meaning (either table or AR). The main problem with changes like these is, to always think about all the side effects such a change might have. Using ‘ShoppingCart[…]’ should be the safer route.

Let’s see, what others think.


(Mh) #12

One more concern, that leads me back to my above suggestion: What if the connection table has many many columns, and we don’t want to always fetch all of them in this case? Defining a specific relation in Product.php would alleviate this:


// Product.php:

public function relations() {

    return array(

        // special relation, only used for a product in a specific cart

        'cart'=>array(self::HAS_ONE, 'ShoppingCart', 'product_id','select'=>'ID,Status,CreateDate'),

    );

}


// User.php

public function relations()

{

    return array(

        'products'=>array(self::MANY_MANY,'Products', 'cart_table(user_id,product_id)','association'=>'cart'),

    );

}




(Creocoder) #13

Yes. This is about i think before this approach. But BELONGS_TO, not HAS_ONE:




// Product.php:

public function relations() {

    return array(

        // special relation, only used for a product in a specific cart

        'cart'=>array(self::BELONGS_TO,'ShoppingCart','product_id','select'=>'quantity,order_date'),

    );

}



This is good. But it’s will not work as expected without ‘associative’ option for MANY_MANY relations type. So, wait what Qiang say about approach.


(Mh) #14

Sorry, i have to disagree ;).

If it where BELONGS_TO, Product would need a field like cart_id. But since Product is referenced from cart_table, it’s the other way round, so it must be HAS_ONE (like a special case of HAS_MANY).


(Creocoder) #15

Ok, but CActiveRecord::HAS_ONE and CActiveRecord::BELONGS_TO relation is not too correct here anyway. I think new relation type like CActiveRecord::ASSOCIATIVE to support ‘associative’ option for CActiveRecord::MANY_MANY would be great.

to Mike

What you think about it?


(Mh) #16

Yeah, thought about that, too.


(Mike) #17

So is there still no real solution to this? I am looking at migrating an existing app and have run into the issue as follows:

Tables:

Mortgage

Person

PersonToMortgage

where PersonToMortgage contains field relationship_type (‘Primary Applicant’,‘Co Applicant’, ‘Realtor’, etc)

In my controller I…

$dataProvider=new CActiveDataProvider(‘Mortgage’,array(

                                    'criteria'=>array(


                                        'with'=>array('person'),    


                               )));

In my view I want…

//Customized listview

<?php $this->widget(‘application.modules.listviewheader.CAxListView’, array(

'dataProvider'=&gt;&#036;dataProvider,


    'sortableAttributes'=&gt;array(


    'person.first_name',


    'person.last_name'=&gt;'Last Name',


),





'itemView'=&gt;'_view',


    'template'=&gt;'{summary}{header}{items}{pager}{summary} '

)); ?>

_view I want…

Mortgage data

foreach($data->person as $person)

 &#036;person.first_name &#036;person.PersonToMortgage.relationship_type

Is there no way to do this with AR? or am i missing something

Thanks

Mike


(Creocoder) #18

mikeax

Very powerful HAS_MANY with "through" option coming… so, very soon you can do as wanted.


(Mike) #19

Thanks creocoder,

I hate to push but can you give me an idea of what ‘soon’ means? Days? Weeks? Months?

I have to make a decision for our organization on a framework and love what i see of Yii so far but this is a bit of a big issue for me.

Thanks for the reply.

Mike


(Creocoder) #20

mikeax

I can say that code and unit tests for this feature is ready. After Sam Dark check and document this, feature will be commited to SVN.