Yii Framework Forum: Selecting join table with mant to many? - Yii Framework Forum

Jump to content

  • (3 Pages)
  • +
  • 1
  • 2
  • 3
  • You cannot start a new topic
  • You cannot reply to this topic

Selecting join table with mant to many? Rate Topic: ***** 3 Votes

#1 User is offline   mech7 

  • Standard Member
  • PipPip
  • Yii
  • Group: Members
  • Posts: 220
  • Joined: 26-March 09

Posted 11 April 2010 - 04:59 AM

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

#2 User is offline   creocoder 

  • Standard Member
  • PipPip
  • Yii
  • Group: Members
  • Posts: 198
  • Joined: 09-March 09
  • Location:*.php

Posted 11 April 2010 - 06:27 PM

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?
No good, no bad, only consequence.
3

#3 User is offline   mech7 

  • Standard Member
  • PipPip
  • Yii
  • Group: Members
  • Posts: 220
  • Joined: 26-March 09

Posted 11 April 2010 - 10:03 PM

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

#4 User is offline   Mike 

  • Elite Member
  • PipPipPipPipPip
  • Yii
  • Group: Members
  • Posts: 3,016
  • Joined: 06-October 08
  • Location:Upper Palatinate

Posted 12 April 2010 - 03:59 AM

@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?
1

#5 User is offline   creocoder 

  • Standard Member
  • PipPip
  • Yii
  • Group: Members
  • Posts: 198
  • Joined: 09-March 09
  • Location:*.php

Posted 12 April 2010 - 07:57 AM

Quote

Nice approach.

Thanks. :rolleyes:

Quote

I think, it requires 2 parameters: the name of the ActiveRecord for the relation table

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.c.../detail?id=1117
No good, no bad, only consequence.
0

#6 User is offline   Mike 

  • Elite Member
  • PipPipPipPipPip
  • Yii
  • Group: Members
  • Posts: 3,016
  • Joined: 06-October 08
  • Location:Upper Palatinate

Posted 12 April 2010 - 08:05 AM

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.
0

#7 User is offline   creocoder 

  • Standard Member
  • PipPip
  • Yii
  • Group: Members
  • Posts: 198
  • Joined: 09-March 09
  • Location:*.php

Posted 12 April 2010 - 08:23 AM

Quote

How would you know the name of the ActiveRecord class? In your case tablename==class name.

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

Quote

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

No, it's full BC. Framework should check class file and if this is not found, it use part of 'cart_table(user_id,product_id)' (i bold part) as table_name.
No good, no bad, only consequence.
0

#8 User is offline   creocoder 

  • Standard Member
  • PipPip
  • Yii
  • Group: Members
  • Posts: 198
  • Joined: 09-March 09
  • Location:*.php

Posted 12 April 2010 - 08:29 AM

Quote

it doesn't require to always create a AR for the connecting table

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.
No good, no bad, only consequence.
0

#9 User is offline   Mike 

  • Elite Member
  • PipPipPipPipPip
  • Yii
  • Group: Members
  • Posts: 3,016
  • Joined: 06-October 08
  • Location:Upper Palatinate

Posted 12 April 2010 - 08:37 AM

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.
1

#10 User is offline   creocoder 

  • Standard Member
  • PipPip
  • Yii
  • Group: Members
  • Posts: 198
  • Joined: 09-March 09
  • Location:*.php

Posted 12 April 2010 - 08:40 AM

Quote

ShoppingCart could either be a table or a AR class name, right?

Yes.

Quote

Things should be unabmiguous.

Please, explain this more.

Quote

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

Yes, it nice too. So framework always know what we mean.
No good, no bad, only consequence.
0

#11 User is offline   Mike 

  • Elite Member
  • PipPipPipPipPip
  • Yii
  • Group: Members
  • Posts: 3,016
  • Joined: 06-October 08
  • Location:Upper Palatinate

Posted 12 April 2010 - 08:51 AM

View Postcreocoder, on 12 April 2010 - 08:40 AM, said:

Please, explain this more.


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.
0

#12 User is offline   Mike 

  • Elite Member
  • PipPipPipPipPip
  • Yii
  • Group: Members
  • Posts: 3,016
  • Joined: 06-October 08
  • Location:Upper Palatinate

Posted 12 April 2010 - 09:01 AM

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'),
    );
}

0

#13 User is offline   creocoder 

  • Standard Member
  • PipPip
  • Yii
  • Group: Members
  • Posts: 198
  • Joined: 09-March 09
  • Location:*.php

Posted 12 April 2010 - 09:09 AM

Quote

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

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.
No good, no bad, only consequence.
0

#14 User is offline   Mike 

  • Elite Member
  • PipPipPipPipPip
  • Yii
  • Group: Members
  • Posts: 3,016
  • Joined: 06-October 08
  • Location:Upper Palatinate

Posted 12 April 2010 - 09:14 AM

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).
0

#15 User is offline   creocoder 

  • Standard Member
  • PipPip
  • Yii
  • Group: Members
  • Posts: 198
  • Joined: 09-March 09
  • Location:*.php

Posted 12 April 2010 - 09:30 AM

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?
No good, no bad, only consequence.
0

#16 User is offline   Mike 

  • Elite Member
  • PipPipPipPipPip
  • Yii
  • Group: Members
  • Posts: 3,016
  • Joined: 06-October 08
  • Location:Upper Palatinate

Posted 12 April 2010 - 09:32 AM

Yeah, thought about that, too.
0

#17 User is offline   mikeax 

  • Junior Member
  • Pip
  • Yii
  • Group: Members
  • Posts: 21
  • Joined: 19-February 11
  • Location:Washington

Posted 26 February 2011 - 09:37 PM

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'=>$dataProvider,
'sortableAttributes'=>array(
'person.first_name',
'person.last_name'=>'Last Name',
),

'itemView'=>'_view',
'template'=>'{summary}{header}{items}{pager}{summary} '
)); ?>

_view I want...

Mortgage data
foreach($data->person as $person)
$person.first_name $person.PersonToMortgage.relationship_type

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

Thanks
Mike
0

#18 User is offline   creocoder 

  • Standard Member
  • PipPip
  • Yii
  • Group: Members
  • Posts: 198
  • Joined: 09-March 09
  • Location:*.php

Posted 27 February 2011 - 09:30 PM

mikeax
Very powerful HAS_MANY with "through" option coming... so, very soon you can do as wanted.
No good, no bad, only consequence.
0

#19 User is offline   mikeax 

  • Junior Member
  • Pip
  • Yii
  • Group: Members
  • Posts: 21
  • Joined: 19-February 11
  • Location:Washington

Posted 27 February 2011 - 11:34 PM

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
0

#20 User is offline   creocoder 

  • Standard Member
  • PipPip
  • Yii
  • Group: Members
  • Posts: 198
  • Joined: 09-March 09
  • Location:*.php

Posted 28 February 2011 - 12:19 AM

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.
No good, no bad, only consequence.
0

Share this topic:


  • (3 Pages)
  • +
  • 1
  • 2
  • 3
  • You cannot start a new topic
  • You cannot reply to this topic

1 User(s) are reading this topic
0 members, 1 guests, 0 anonymous users