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.

We're retiring the forums!

The SilverStripe forums have passed their heyday. They'll stick around, but will be read only. We'd encourage you to get involved in the community via the following channels instead:

Data Model Questions /

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

Join over multiple Tables

Go to End

2 Posts   2328 Views


Community Member, 146 Posts

23 March 2011 at 9:24pm

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?




Community Member, 473 Posts

24 March 2011 at 8:01am

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.