MANY_MANY question

Hello,

I have the following table setup




++++++++++++++                     +++++++++++++

|  TABLE A   |                     +  TABLE B  +

++++++++++++++                     +++++++++++++

| id         |---  +++++++++++  ---| id        |

| name       |  |  + A_AND_B +  |  | something |

++++++++++++++  |  +++++++++++  |  +++++++++++++

                -->| aID     |  |

                   | bID     |<--

                   | status  |

                   +++++++++++




I successfully set up the MANY_MANY relationship between these tables:




  // TABLE A relations

  return array(

    'tableb' => array( self::MANY_MANY, 'TABLE B', 'A_AND_B(aID,bID)' )

  )



and it works great and as I expected, but is there a way for me to get the [i]status[/i] column from my table A_AND_B?

I also tried to add the [i]together[/i] param, but didn’t help…

thanks,

–iM

not elegant solution, but what if you create another relation between TableA and A_AND_B?

agree with @zitter,


return array(

    'tableb' => array( self::MANY_MANY, 'TABLE B', 'A_AND_B(aID,bID)' ),

    'tableab' => array( self::HAS_MANY, 'A_AND_B', 'aID' ), //another relation between Table A and A_AND_B

  );

btw, thanks for the podcasts. I do like it. :lol:

I had the same problem before… maybe it be of some help :P I still haven’t found an elegant solution yet :)

http://www.yiiframework.com/forum/index.php?/topic/8011-many-many-how-to-select-some-fields-from-the-link-table

http://www.yiiframework.com/forum/index.php?/topic/8581-selecting-join-table-with-mant-to-many/page__fromsearch__1

well in my case, I added a condition, and it’s enough right now (i just want to make sure that status is 1)

so it looks something like this:




return array(

    'tableb' => array( self::MANY_MANY, 'TABLE B', 'A_AND_B(aID,bID)', 'condition' => '`status`=1' )

  )



not pretty but does the trick

–iM

I always solve this problem by generating a tableA_has_tableB Model to operate with.

Why not use a model for the ‘A_and_B’ table???

rikardo,

I don’t wanna create a model every time I have a pivot table. (ok, it’s a little more than that in this case)

btw, I used to do that :)

–iM

hi, ive got a table like this.

Table : products (products_id, name)

Table: categories(categories_id, name)

Table: products_categories(products_id, categories_id)

before i did this in my controller




  $criteria = new CDbCriteria;

		  $criteria->condition = "products_id = :value";

		  //$criteria->params = array(":value"=>"%$name%");

		  $criteria->params= array(":value" => "1");


$dataProvider=new CActiveDataProvider('products',




now i like to search for all products in category 1.

so i change this in my models

Products.php




	public function relations()

	{

		

		return array('categories'=>array(self::MANY_MANY, 'Categories', 'products_categories(categories_id,products_id)')


		);

	}




Categories.php




	public function relations()

	{

		

		return array('products'=>array(self::MANY_MANY, 'Products', 'products_categories(categories_id,products_id)')


		);

	}



how have i change my query to get all products in category 1.?

anyone can help me ?