cgridview with many to many

rmulhern · February 22, 2011, 4:43pm

I’m trying to get some data from a join table in a many-to-many relationship in a cgridview.

I have 3 tables: user, card, and user_card. User has an ID and some data, card has an ID and a set_id and some data, user_card has an id, a user_id, a card_id, and a quantity field. So a user can have a card with a quantity associated with it.

There is a model for the User and Card tables with many-to-many relationships set up in them.

The data I want to get is all cards within a set and then display them with the quantity the logged in user has. The SQL for this would be SELECT * FROM card LEFT JOIN member_card ON card.id = member_card.card_id AND member_card.member_id =1 WHERE set_id =1;

It seems I can’t create a cgridview that would allow me to access both the card data with the data from the user_card quantity field. Is it possible to do this?

tri · February 22, 2011, 6:42pm

rmulhern:

I’m trying to get some data from a join table in a many-to-many relationship in a cgridview.

I have 3 tables: user, card, and user_card. User has an ID and some data, card has an ID and a set_id and some data, user_card has an id, a user_id, a card_id, and a quantity field. So a user can have a card with a quantity associated with it.

There is a model for the User and Card tables with many-to-many relationships set up in them.

The data I want to get is all cards within a set and then display them with the quantity the logged in user has. The SQL for this would be SELECT card.id, card.name, user_card.quantity FROM card LEFT JOIN user_card ON card.id = user_card.card_id WHERE card.set_id = 1 AND user_card.user_id = 1;

It seems I can’t create a cgridview that would allow me to access both the card data with the data from the user_card quantity field. Is it possible to do this?

It can be done like this:

Generate all three models with Gii (assuming mySQL, innodb, autoincrement id’s, constraints added).
Create crud for the UserCard model.
In the UserCardControllers admin view, in the grids column spec, replace user_id and card_id with user.some_data and card.some_attribute respectively.
Add selection criteria to the UserCard models search() method.

/Tommy

rmulhern · February 22, 2011, 11:34pm

Thanks for the reply but the problem with this is that it only gets the records for all the cards with a record in the UserCard table.

I want all the Cards and if it has a record in the UserCard table I’d like that as well.

rmulhern · February 22, 2011, 11:51pm

actually I couldn’t get it to work at all.

Here are the relations in my UserCard model:




	public function relations()

	{

		return array(

	        'card'=>array(self::BELONGS_TO, 'Card', 'card_id'),

	        'user'=>array(self::BELONGS_TO, 'User', 'user_id')

		);

	}

Here is my grid in my view:




<?php $this->widget('zii.widgets.grid.CGridView', array(

	'id'=>'user-card-grid',

	'dataProvider'=>$model->search(),

	'filter'=>$model,

	'columns'=>array(

		'id',

		'username',

		'card_name',

		'have',

		'want',

		array(

			'class'=>'CButtonColumn',

		),

	),

)); ?>

and my model’s search criteria




		$criteria->compare('id',$this->id);

		$criteria->compare('user_id',$this->user_id);

		$criteria->compare('card_id',$this->card_id);

		

		$criteria->compare('card_name',$this->card->name);

		$criteria->compare('username', $this->user->username);

		

		$criteria->compare('have',$this->have);

		$criteria->compare('want',$this->want);

I get the error: Property "UserCard.username" is not defined.

gusnips · February 23, 2011, 1:31am

Its is because the related does not always exist

do something like




        'columns'=>array(

                'id',

                array(

                    'name'=>'username',

                    'value'=>'isset($data->username)?$data->username : "null"',

                 ),

                'card_name',

                'have',

                'want',

                array(

                        'class'=>'CButtonColumn',

                ),

        ),

rmulhern · February 23, 2011, 2:06am

Gustavo:

Its is because the related does not always exist

do something like




        'columns'=>array(

                'id',

                array(

                    'name'=>'username',

                    'value'=>'isset($data->username)?$data->username : "null"',

                 ),

                'card_name',

                'have',

                'want',

                array(

                        'class'=>'CButtonColumn',

                ),

        ),

I still get the same error.

tri · February 23, 2011, 2:26am

In this case you can write user.username (that is the ‘user’ relationship which is BELONGS_TO), in some other cases you must use the array format like Gustavo pointed out.
You can list all cards with one UserCard record if you use the crud for the Card model (card/admin) instead.




...

set_id,

somedata,

array(

  'name'=>'quantity',

  'value'=>'$data->card?$data->card[0]->quantity:NULL',

),

...

There seems to be something wrong with your data model. You want to list ALL cards, there is a MANY_MANY relationship to user, but you claim that not all cards have a corresponding record in the association table. How can you show all cards belonging to a particular user without a record in the association table connecting the card to a user.

/Tommy

rmulhern · February 23, 2011, 3:04am

I don’t want to show all cards belonging to a user. I want to show all cards, but if the user owns a card I want to show the qty they own from the user_card table.

rmulhern · February 23, 2011, 6:33am

Still struggling with this. The closest thing I can do to get the data to work is to add another relationship, quantities, to the Card model:




	'users'=>array(self::MANY_MANY, 'User', 'user_card(card_id, user_id)'),

	'quantities'=>array(self::HAS_MANY, 'UserCard', 'card_id')

This doesn’t help with the CGridView though. I am only able to do something like this:




	$cards = Card::Model()->with('quantities')->findAll('quantities.user_id=1');


	foreach($cards as $card) {

		echo $card->id.' - '.$card->quantities[0]->qty.'<br>';

	}

That’s not ideal for what I want to do. I just hope it helps explain what I’d like to do a little more.

** Update *****

Actually after looking at it more the above code doesn’t do what I would like to do, it just lists the cards the user has a record for in UserCards. I really want all cards.

mdomba · February 23, 2011, 7:13am

Check this thread, maybe it will give you some ideas - http://www.yiiframework.com/forum/index.php?/topic/14390-

rmulhern · February 23, 2011, 1:05pm

Helps somewhat, but my problem is a little different.

Doesn’t seem like they ever gets the is_highlighted field. Or at least it’s not discussed how they resolved that in the thread. That’s very similar to what I want to do.
It seems like they will always have a company-category record. That’s not the case in my situation. I want to join_user card for every card, but there may be a record or there might not be a record in user_card for that card_id. Even if there is it might not be for the right user_id. So I can set up a has many and another method that gets the qty, but that method would have to go through all the user_ids and make sure that it’s for that user. That seems very inefficient.

Is there a way I can do one of these two things?

Either add a condition to the my quantity relation that says something like user_card.user_id = $user_id




	'users'=>array(self::MANY_MANY, 'User', 'user_card(card_id, user_id)'),

	'quantity'=>array(self::HAS_ONE, 'UserCard', 'card_id')

or when I do this in my search() I get the error Property "Card.user_card" is not defined. or Property "Card.qty" is not defined.




	$criteria=new CDbCriteria;


	$criteria->alias = 'card';		

	$criteria->join='LEFT JOIN user_card ON user_card.card_id = card.id AND user_card.user_id = 1';


	//error card.user_card is not defined

	//$criteria->compare('qty', $this->user_card->qty);


	//error card.qty is not defined

	//$criteria->compare('qty', $this->qty);


	$criteria->compare('id',$this->id);

	$criteria->compare('name',$this->name,true);

rmulhern · February 23, 2011, 3:08pm

rmulhern:




	$criteria=new CDbCriteria;


	$criteria->alias = 'card';		

	$criteria->join='LEFT JOIN user_card ON user_card.card_id = card.id AND user_card.user_id = 1';


	//error card.user_card is not defined

	//$criteria->compare('qty', $this->user_card->qty);


	//error card.qty is not defined

	//$criteria->compare('qty', $this->qty);


	$criteria->compare('id',$this->id);

	$criteria->compare('name',$this->name,true);

I think I might be using compare incorrectly. Is it possible something like this makes more sense? Sorry, I couldn’t test it since I’m at work now. But this may be my problem?




	$criteria=new CDbCriteria;


	$criteria->alias = 'card';		

	$criteria->join='LEFT JOIN user_card ON user_card.card_id = card.id AND user_card.user_id = 1';


	$criteria->compare('user_card.qty', $this->qty);


	$criteria->compare('id',$this->id);

	$criteria->compare('name',$this->name,true);

I’m certainly not new to PHP, MVC, or ORM… just Yii. This problem has been bothering me all morning and all I want to do is get it to work.

rmulhern · February 23, 2011, 11:22pm

Ok, maybe I’m getting closer. I can get it to list all the cards and all the quantities but it 1. displays the quantities for all users. 2. Displays a record for each card and user combination. So if 2 users have card 4 it shows 2 records for card 4. Even if I make the relationship HAS_ONE.

Here are my relationships in my Card model:




	public function relations()

	{

		return array(

			'users'=>array(self::MANY_MANY, 'User', 'user_card(card_id, user_id)'),

			'user_qty'=>array(self::HAS_ONE, 'UserCard', 'card_id')

		);

	}

and here is my Card::search() which does seem to be applied:





	$criteria=new CDbCriteria;


	$criteria->alias = 'card';		

	$criteria->join='LEFT JOIN user_card ON user_card.card_id = card.id AND user_card.user_id = 2';


	$criteria->compare('id',$this->id);

	$criteria->compare('name',$this->name,true);


	return new CActiveDataProvider(get_class($this), array(

		'criteria'=>$criteria,

	));

this is my grid:




<?php $this->widget('zii.widgets.grid.CGridView', array(

	'id'=>'card-grid',

	'dataProvider'=>$model->search(),

	'filter'=>$model,

	'columns'=>array(

		'id',

		'name',

		array(

			'header'=>'test',

			'value'=>'isset($data->user_qty->qty)?$data->user_qty->qty:""'

		),

		array(

			'class'=>'CButtonColumn',

		),

	),

)); ?>

This has to have been done before! I can’t imagine anyone making an inventory, shopping cart, or collection application without this functionality. Anyone?

tri · February 24, 2011, 12:33am

This is what you can do for this particular query




public function relations()

{

  return array(

    'userCards'=>array(self::HAS_MANY, 'UserCard', 'card_id'),

  );

}

...

public function search()

{

  $criteria=new CDbCriteria;


  $criteria->with = array(

    'userCards'=>array(

      'alias'=>'uc',

      'on'=>"uc.user_id = $user",

      'together'=>true,

    )

  );

  return new CActiveDataProvider(get_class($this), array(

    'criteria'=>$criteria,

  ));

}

Next, you can extend this to also join with the user relationship in the UserCards model




  $criteria->with = array(

    'userCards'=>array(

      ...

    )

    'userCards.user'=>array(

      ...

    )

    ...

  );

/Tommy

rmulhern · February 24, 2011, 4:51am

Yes! That finally worked! Thanks, everyone.

meadows · November 23, 2011, 3:40am

Any chance you feel like showing exactly what worked?