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   1762 Views

Avatar
Andre

Community Member, 126 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?

regards

Andre

Avatar
simon_w

Forum Moderator, 474 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.