Skip to main content

This site requires you to update your browser. Your browsing experience maybe affected by not having the most up to date version.

Data Model Questions /

[SOLVED] Return Left Join on Raw SQL Query SS3


Reply


3 Posts   355 Views

Avatar
Optic Blaze

Community Member, 165 Posts

3 January 2014 at 9:51pm

Hi,

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\"")
      ->where
      ("
      (Tel1 = '$tel1' OR Tel1 = '$tel2' OR Tel1 = '$tel3')
      (ClaimNo = '$claim')
       ");
      return $this->customise(array(
'Results' => $customer
   ))->renderWith(array('CustomerSearch','Page'));
      

Avatar
martimiz

Forum Moderator, 1106 Posts

5 January 2014 at 12:08am

Edited: 05/01/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?

[edit] 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?

Avatar
Optic Blaze

Community Member, 165 Posts

12 January 2014 at 2:59am

Hi,

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(
   'Claims'=>'Claim'
   );

and then on the Claim.ss template i looped the claims like this:
<% loop $Result%>
<% loop $Claims %> $ClaimNo, <% end_loop %>
<% end_loop %>