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

Join over multiple Tables


Reply

2 Posts   1708 Views

Avatar
Andre

23 March 2011 at 9:24pm Community Member, 126 Posts

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

Avatar
simon_w

24 March 2011 at 8:01am Forum Moderator, 474 Posts

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.