As you can see, I have two tables Customer and Services that are in a Many to Many relationship. Many Customers can have Many Services, and Many Services can have Many Customers. These tables are linked together via the pivot table Customer_Services.
You can also see that the pivot table has two additional columns, start_date and end_date.
The design is to allow us to track when a Customer first starts to use a service and also when they stopped.
In Yii in my Customer and Services models, I can successfully use the MANY_MANY relationships to get related data in both directions. However, I still don’t have any idea how to also grab the contents of the pivot table.
Of course, I could use a HAS_MANY relationship in my Customer model with the pivot table to get the dates, but then in my views I would have:
$data->services //MANY_MANY relationship result with services
$data->customer_services //HAS_MANY relationship result with dates
So now I have two arrays of results to deal with. This is not particularly elegant as I would have to iterate both arrays and hope to god the right dates line up with each service.
My question then is how do you retrieve related records from both a MANY_MANY table and a pivot table in a single query with Yii? And, if possible, how would you go about sorting those results by date [or even omitting some results depending on the date]?
I have been looking at everything to do with MANY_MANY relationships and nothing seems to quite answer what I am after.
Why not use the model for the CustomerServices table, and there define BELONGS_TO one for customer and one for service. And like its name, that will be the pivot which you generate your views from, so you can search for any CustomerServices between two dates, and the results already contain the Customer and Service records via relationship.
I tried your approach, but I have run into a problem.
in my _view.php for Customer, I have the following chunk of code:
<b>Current Services:</b>
<?php
$now = time();
$criteria = new CDbCriteria();
//we only want records belonging to current customer
$criteria->condition = "idcustomer=$data->idcustomer";
//and only those records that are still current
$criteria->condition = "$now < end_date";
$result = CustomerServices::model()->with('services')->findAll($criteria);
echo $data->idcustomer;
echo "<br />";
foreach($result as $row)
{
echo $row->start_date;
echo " - ";
echo $row->services->service_name;
echo "<br />";
}
?>
The records returned are correct in that they match the date and it only returns current services. However, every customer has every single service - even if that customer has no services belonging to them at all.
For example, customer 1 has Service A, customer 2 has Service B. Yet every customer in the list shows as having Service A and Service B - even if they have no services at all. I am unsure how to alter the above code, or the CustomerServices model, to make it behave.
The relation in CustomerServices is defined as below:
public function relations()
{
return array(
'services' => array(self::BELONGS_TO, 'Services', 'idservices'),
'customer' => array(self::BELONGS_TO, 'Customer', 'idcustomer'),
);
}
Ahh, OK. Will have to take that bit out tomorrow and see if it falls to bits or not!
I solved my problem anyway. I mistakenly believed using
$criteria->condition = "condition"
multiple times would build up one large WHERE clause.
What was happening in reality is the condition it be only a particular customer id was being overwritten, so I was getting everything every time.
I changed the code to just one condition statement and all works as it should.
Thank you for your help and pointing me in the right direction! As it happens, having a CustomerServices pivot model helped me to also create new records in that table when updating/creating a customer.
