Need help with AR and Scope

Hi,

I have 3 tables: User, Event and Order.

Each user can create new event and multiple users can make an order to an event.

So user will have two roles, event creator and orderer. I will focus on orderer.

For example, there are:

Event1 [ order1, order2, order3 ]

Event2 [ order4, order5, order6 ]

Event3 [ order7, order8, order9 ]

UserA is the orderer of [ order1,order4,order7 ]

UserB is the orderer of [ order3, order 5 ]

What I want to try is get all Events relation with orders where userB involved.

My expected result is ( Event1 and Event2 ) since UserB’s orders are [ order3 and 5 ]

I tried with scopes but not the one I want.

Please shed me a light kindly.

Thanks in advance!

Hi, anyone?

Please let me know if you guys need to be explained more or provide anything.

Or is that too dumb to ask :)

Hi Devyn, welcome to the forum.

No, not at all. :D

I think it’s rather too complicated a question to answer quickly. You have to give us more information, for example, 1) the relations you have established among User, Event and Order 2) the scope you defined to get the expected result, … etc. :)

hi

an easy way to do this is as follows

your tables may be something like these:





CREATE  TABLE `User` (


  `user_id` INT  NOT NULL PRIMARY KEY,

  `name` VARCHAR(128),

   /* other columns */


);


CREATE  TABLE `Event` (


  `event_id` INT  NOT NULL PRIMARY KEY,

  `user_id` INT,

  /* other columns */


);


CREATE  TABLE `Order` (


  `order_id` INT  NOT NULL PRIMARY KEY,

  `event_id` INT,

  `user_id` INT,

   /* other columns */


);



and in your models you have to declare relations as follows:





//relations for Event table

public function relations()

{

   return array(

     'orders' => array(self::HAS_MANY, 'Order', 'event_id'),

   );

}


//relations for Order table

public function relations()

{

   return array(

     'event' => array(self::BELONGS_TO, 'Event', 'event_id'),

   );

}




//relations for User table

public function relations()

{

   return array(

     'events' => array(self::HAS_MANY, 'Event', 'user_id'),

     'orders' => array(self::HAS_MANY, 'Order', 'user_id' ,'with'=>'event'),

   );

}



in lazy loading way you can access events of a user’s orders as follows




$user=User::model::findByPk($id);

$orders=$user->orders;

foreach($orders as $o)

{

    $oEvents[]=$o->event;

}



but this isn’t efficient to use a loop like this to retrieve data, I think you can use Relational Query with through (refer to Definitive Guide)

but as I’m new to yii and didn’t used “through”, I prefer do it by sql and DAO instead of AR.