Jump to:

3462 Posts in 1065 Topics by 740 members

Data Model Questions

SilverStripe Forums » Data Model Questions » [SOLVED] Return Left Join on Raw SQL Query SS3

Moderators: martimiz, Sean, biapar, Willr, Ingo, swaiba, simon_w

Page: 1
Go to End
Author Topic: 323 Views
  • Optic Blaze
    Community Member
    162 Posts

    [SOLVED] Return Left Join on Raw SQL Query SS3 Link to this post


    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')
          return $this->customise(array(
    'Results' => $customer

  • martimiz
    Forum Moderator
    1091 Posts

    Re: [SOLVED] Return Left Join on Raw SQL Query SS3 Link to this post

    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?

  • Optic Blaze
    Community Member
    162 Posts

    Re: [SOLVED] Return Left Join on Raw SQL Query SS3 Link to this post


    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 %>

Page: 1
Go to Top

Want to know more about the company that brought you SilverStripe? Then check out SilverStripe.com

Comments on this website? Please give feedback.