Jump to:

3460 Posts in 1064 Topics by 739 members

Data Model Questions

SilverStripe Forums » Data Model Questions » Join over multiple Tables

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

Page: 1
Go to End
Author Topic: 1696 Views
  • Andre
    Avatar
    Community Member
    126 Posts

    Join over multiple Tables Link to this post

    Hi there,

    I need to sort my Dataobject Set by Attributes of related Dataobjects. The Problem is that im my case the Objects to sort are related to two further Objects that need to be taken for sorting.

    In Short, I have a a Customer, who has Projects and each Project has Tasks.

    Now I want to sort all Tasks first by Customer.Title and Second by Project Title.

    I know, I can simply sort all Customers, than all Projects, than all Tasks, but I want to have the Tasks paginated.

    If only for example sorting by Project.Title the DataObject::get looks the following:

    $Tasks = DataObject::get("Task", "", "Project.Title ASC, Task.Title ASC", "LEFT JOIN Project ON Project.ID = Task.ProjectID", (int)$_GET['start'].",10");

    Question now is, how do I need to modify this, to have a left join on more than one extra Table, btw is there another way, to sort by Attributes of related tables?

    The regular SQL Join would look the following:

    select * from (Task left join Project on Task.ProjectID = Project.ID) left join Customer on Task.CustomerID = Customer.ID ORDER BY Customer.Title ASC, Project.Title ASC, Task.Title ASC;

    As for my Understanding, the first Parameter of DataObject::get() is taken for the "FROM" Statement and the type of the returned Dataobject, there will be no way, to receive A DataObjectSet sorted by multiple Tables, correct?

    regards

    Andre

  • simon_w
    Avatar
    Forum Moderator
    473 Posts

    Re: Join over multiple Tables Link to this post

    Just pass your two join clauses as a single string as the join argument. Joins are done from left to write so you shouldn't need to get brackets in there.

    1696 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.