Complex query using ORM

Discuss about Group-Office development here

Moderator: Developers

richarddodd
Posts: 66
Joined: Fri Jun 21, 2013 2:55 pm

Complex query using ORM

Postby richarddodd » Mon Jun 09, 2014 4:28 pm

Hi

I've been using the GO ORM and I like it, but sometimes I struggle to do more complex queries, falling back on raw sql. For example:

Code: Select all

SELECT `ta`.`a`, `ta`.`b`, `ta`.`c` ...., `tb`.`a`, `tb`.`b`, `tb`.`c`, ....
FROM `table_a` `ta`
INNER JOIN `table_b` `tb`
    ON `tb`.`ta_id` = `ta`.`id`
WHERE 1
AND NOT EXISTS (
    SELECT null FROM `table_b` `tb2`
    WHERE `tb2`.`ctime` > `tb`.`ctime`
)


The idea here is that there is a '1 - many ' relationship between `table_a` and `table_b` but in fact we want to get a '1 - 1' relationship (because our subquery garuntees only the record with the latest ctime is returned, if 2 table_b records have the same time ignore 2nd one onwards).

I would like to be able to do this with the ORM. I can do the sql using FindParams (although it is a bit arduous), but I don't know how to get the returned data into objects (so I could do

Code: Select all


$table1 
= \GO\Foo\Model\Table1::model()->find($myFParams);
echo $table1->table2->prop;
 


Have you got any ideas?

NOTE: This is the best way to do "WHERE NOT IN" style queries - no temp tables and no full table scans :).
mschering
Site Admin
Site Admin
Posts: 8154
Joined: Tue Apr 20, 2004 1:06 pm
Location: The Netherlands - Den Bosch
Contact:

Re: Complex query using ORM

Postby mschering » Tue Jun 10, 2014 1:45 pm

Sub queries are not really supported. You'll have to do a ->addRawCondition() to add it.
Best regards,

Merijn Schering
Intermesh

Return to “Development Chats”

Who is online

Users browsing this forum: No registered users and 1 guest

cron