3372 Posts in 998 Topics by 712 members
|Go to End|
3 January 2014 at 9:51pm
According to http://doc.silverstripe.org/framework/en/topics/datamodel when you do a raw SQL query and you use a left join the object created will not return the additionally joined data. In the example below i need to search for both customers' phone numbers as well as claim numbers. The phone numbers is stored in the Customer object and the claim numbers in the Claim object. There is a one to many relationship between Customers and claims. I need the sql query to return the claim numbers as well. How do i do that?
$customer = Customer::get()
->leftJoin("Claim", "\"Claim\".\"CustomersID\" = \"Customer\".\"ID\"")
(Tel1 = '$tel1' OR Tel1 = '$tel2' OR Tel1 = '$tel3')
(ClaimNo = '$claim')
'Results' => $customer
5 January 2014 at 12:08am Last edited: 5 January 2014 12:18am
I'm sure It cannot be done using Customer::get(). It could probably be done by using an SQLQuery, but you'd end up with a recordset, where there would be multiple records for each customer - one for each claim...
In the current context you'd get a list of Customer objects, and each Customer will let you loop and/or filter its claims... Could you explain why that wouldn't work in your specific case?
 also, looking at your query, you could approch this the other way around, by doing a $claim = Claim::get()->filter('number' => $xxxx)->first() on the claim number, and then check the $claim->Customer() validity? Or something in that direction?
12 January 2014 at 2:59am
Thanks for the advice. I looped on the relationship within $Results to get a list of claims for that specific customer.
So now i have one customer with a list of all his claims instead of the same customer repeated many times which is what i would have had if the left join did what i originally wanted...this way is much better.
So for any future readers...i had a has many relationship like this:
public static $has_many = array(
and then on the Claim.ss template i looped the claims like this:
<% loop $Result%>
<% loop $Claims %> $ClaimNo, <% end_loop %>
<% end_loop %>
|Go to Top|