how to implement a sorting subquery in the 'from' section?

Question

I need to have a subquery order a table before joining so that when I group the table, the proper collapsed data is shown. For the example query, I want the latest start time before a given datetime (2006-08-26 00:00:00)

select * from
parts p left join 
(select * from 
transactions t 
left join 
transactiondetails td 
on(td.transaction_id=t.id and t.type='loc' and t.start<='2006-08-26 00:00:00')
order by start desc) t
on (t.part_id=p.id)
group by p.id

can cakePHP do this. If not, how would I run a custom query like this, given that views with subqueries in the from clause cannot be saved?

Answer 1

I haven't seen Cake supporting nested queries. However you can simply pass this query as-it-is to Model->query().