Jump to:

3433 Posts in 1058 Topics by 734 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: 293 Views
  • Optic Blaze
    Avatar
    Community Member
    161 Posts

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

    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'));
          

  • martimiz
    Avatar
    Forum Moderator
    1068 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
    Avatar
    Community Member
    161 Posts

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

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

    293 Views
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.