Aggregates: make them smarter (nested joins)

By Kilian Hekhuis on 30 Jan 2015
Here's an improvement to aggregates I'd love to see, and that's making them smarter. Currently, when left joining table B to A (A with or without B), then inner joining table C to B (B only with C), the aggregate yields nothing if B doesn't exists. The aggregate could be smarter: when seeing an inner join to a table that's left joined elsewhere, it should produce a nested inner join. Now I'm forced to make an advanced query in these cases.
Paulo Ferreira31 Jan 2015
If I'm understanding the scenario correctly, wouldn't swapping the joins have the desired result? First give me all Bs with Cs, then give me all As with or without Bs.
Paulo Ferreira31 Jan 2015
If I'm understanding the scenario correctly, wouldn't swapping the joins have the desired result? First give me all Bs with Cs, then give me all As with or without Bs.
Paulo Ferreira31 Jan 2015
If I'm understanding the scenario correctly, wouldn't swapping the joins have the desired result? First give me all Bs with Cs, then give me all As with or without Bs.
Kilian Hekhuis2 Feb 2015
No, I don't think so. We have three tables, let's call them A, B and C. A and B have a 1:n relation, where C defines the type of B (it should've been a static entity, but this is old stuff, dating from pre-platform 5). What I want is a record A (identified by its Id), and its record B of a specific type C. So no B if there's no B of type C. In an advanced query, I'd do this:

SELECT {A}.*, {B}.*
FROM {A}
LEFT JOIN {B}
    INNER JOIN {C} ON {C}.[B_Id] = {B}.[Id]
        AND {B}.[Code] = 'X'
   ON {B}.[A_Id] = {A}.[Id]

So we have a nested inner join limiting B, which is left joined to C. I really can't see how I can do that in a single aggregate.
Kilian Hekhuis2 Feb 2015
EDIT: left joined to A
Paulo Ferreira2 Feb 2015
The order of the joins in the Aggregate allows you to specify their priority/order by which they should be considered. So having:
  1. B Only With C (C.B_Id = B.Id AND C.Code = "X")
  2. A With or Without B (B.A_Id = A.Id)
Should generate a query similar to (adding parenthesis for clarity):

SELECT ...
FROM A LEFT JOIN (B INNER JOIN C ON C.B_Id AND C.Code = 'X') ON B.A_Id = A.Id

While swapping the order, would generate a query similar to:

SELECT ...
FROM (A LEFT JOIN B ON B.A_Id = A.Id) INNER JOIN C ON C.B_Id AND C.Code = 'X'
Kilian Hekhuis3 Feb 2015
Hi Paulo,

I hadn't considered what you are writing, as I'm pretty sure that's not how simple queries were working (I don't think the order of conditions mattered much). I'll test it and report back...
Paulo Ferreira6 Feb 2015
Hi Kilian,

Did you have the chance to test it? Simple Queries already had that behaviour, but it only applied to outer joins (With or Without, Both). Inner joins (Only Withs) were always considered first, regardless of the order, so you couldn't have this particular configuration.
Kilian Hekhuis9 Feb 2015
Hi Paulo,

I've tested it a bit, and it indeed seems to work this way. It's a pitty it doesn't seem to be documented anywhere, but at least we can have this idea as some form of documentation.

Still, I'd be happier if it was clearer in the interface that this kind of ordering is done.