SOLVED - XPDO Join a subquery

Development
1

I’ve read a few posts in the old forum and a couple of blog posts but still can’t get my head around this simple (I assumed) task.

I have tables A and B. B holds child records for records in A, A has about 100,000 records so performance is a definite consideration too. These children have one of two types, 1 or 2 with a foreign key of a_id linking the tables. I’m in a processor so I need to produce a query object. I also need to sort on a createdDate field in A.

I want to get all records from A with children from B of type 1.

There are a few ways I could do this in straight SQL. For example I could do.


SELECT * FROM A INNER JOIN (SELECT DISTINCT a_id FROM B WHERE type = 1) distinctB on distinctB.a_id = A.id ORDER BY A.createdDate ASC;

But I can’t figure out how to express this as XPDO. I may wish to take advantage of the benefits of XPDO (caching etc) at some point so I’ve been trying…

I have experimented with subqueries and even selecting from both tables before executing a LEFT JOIN but to no avail. I have dug into the XPDOQuery class to try to figure out some way to manipulate the SQL produced by prepare(). Nothing I’ve tried either gives me valid SQL or the query never completes in anything like a reasonable time.

I guess I could flip the relation, doing a distinct query on B and joining A but for learning purposes I’d like to understand the translation of the query as it is.

Pretty sure this would be useful to others too.

2

OK, feeling dumb but on the off chance it helps someone else if I admit it…

I realised I can get what I want with a simple right join.


SELECT DISTINCT A.id FROM A RIGHT JOIN B on A.id = B.a_id WHERE type = 1 ORDER BY createdDate ASC;
2 Likes