alsoFetch: with OUTER JOIN?

I'd like to fly another round of performance tuning on some central parts of my application. So I'd like to do things like this:


query :=
        (SimpleQuery returningManyOf: Vehicle where: [:ea | ea color = 'green'])
            orderBy: [:ea | ea whatever descending];
            alsoFetch: [:ea | ea wheels];
            alsoFetch: [:ea | ea engine];
            alsoFetch: [:ea| ea engine controller] .

Unfortunatley, this query only finds green verhicles that have wheels as well as an engine and whose engine is equipped with a controller. If a vehicle has no engine, it will not be in the result set.

What I'd hope to get is a list of *all* green vehicles,
* with all their wheels (if they have some),
* with their engine (if they have one)
* and their engine's controller (if there is one).

Or, put differently: I want to find

* all green verhicles that have neither wheels nor an engine
* all green verhicles that have any number of wheels, together with their wheels
* all green vehicles with or without wheels, with an engine
* all green vehicles with or without wheels with an engine and a controller


So I think what I am asking for is a way to express this query as an outer join. Neither Query nor AbstractReadQuery understand #beOuterJoin, unfortunately. And to be honest, I am not even sure if an outer join would work for this (my SQL know how is limited).

Does Glorp have any tricks in stock for my use case? I want to avoid ending up with three additional queries for each green vehicle to get their wheels, engine and their engine's controller. So even if I have to collect a list of ids in one statement and do one select for all engines of cars with an engine by id, this would still be a huge speedup compared to following the proxies with single selects for each vehicle (in the worst case 3 SQL statements once the vehicle itself is loaded, so for 1000 vehicels, resolving proxies individually means 3001 SQL queries, plus one for each car with a controller...).

Any ideas or hints?

Joachim

You can send asOuterJoin within the query block of each alsoFetch:  Or to the block once it's been converted to an expression (or something similar, details left as an exercise for the reader).

Alan,

terrific. fanstastic. thank you. As always, I'm blown away.

The whole trick is this:

query :=
        (SimpleQuery returningManyOf: Vehicle where: [:ea | ea color = 'green'])
            orderBy: [:ea | ea whatever descending];
            alsoFetch: [:ea | ea wheels];
            alsoFetch: [:ea | ea engine beOuterJoin];
            alsoFetch: [:ea| ea engine controller beOuterJoin] .

That's really all. Can't believe I didn't try.

Joachim

Am Freitag, 20. Januar 2017 22:00:50 UTC+1 schrieb alan.knight:

You can send asOuterJoin within the query block of each alsoFetch:  Or to the block once it's been converted to an expression (or something similar, details left as an exercise for the reader).

 

On Fri, Jan 20, 2017 at 12:52 PM jtuchel <<a href="javascript:" target="_blank" gdf-obfuscated-mailto="2Q91P4rbAQAJ" rel="nofollow" onmousedown="this.href=&#39;javascript:&#39;;return true;" onclick="this.href=&#39;javascript:&#39;;return true;">jtu...@...> wrote:

I'd like to fly another round of performance tuning on some central parts of my application. So I'd like to do things like this:


query :=
        (SimpleQuery returningManyOf: Vehicle where: [:ea | ea color = 'green'])
            orderBy: [:ea | ea whatever descending];
            alsoFetch: [:ea | ea wheels];
            alsoFetch: [:ea | ea engine];
            alsoFetch: [:ea| ea engine controller] .

Unfortunatley, this query only finds green verhicles that have wheels as well as an engine and whose engine is equipped with a controller. If a vehicle has no engine, it will not be in the result set.

What I'd hope to get is a list of *all* green vehicles,
* with all their wheels (if they have some),
* with their engine (if they have one)
* and their engine's controller (if there is one).

Or, put differently: I want to find

* all green verhicles that have neither wheels nor an engine
* all green verhicles that have any number of wheels, together with their wheels
* all green vehicles with or without wheels, with an engine
* all green vehicles with or without wheels with an engine and a controller


So I think what I am asking for is a way to express this query as an outer join. Neither Query nor AbstractReadQuery understand #beOuterJoin, unfortunately. And to be honest, I am not even sure if an outer join would work for this (my SQL know how is limited).

Does Glorp have any tricks in stock for my use case? I want to avoid ending up with three additional queries for each green vehicle to get their wheels, engine and their engine's controller. So even if I have to collect a list of ids in one statement and do one select for all engines of cars with an engine by id, this would still be a huge speedup compared to following the proxies with single selects for each vehicle (in the worst case 3 SQL statements once the vehicle itself is loaded, so for 1000 vehicels, resolving proxies individually means 3001 SQL queries, plus one for each car with a controller...).

Any ideas or hints?

Joachim

--
You received this message because you are subscribed to the Google Groups "glorp-group" group.
To unsubscribe from this group and stop receiving emails from it, send an email to <a href="javascript:" target="_blank" gdf-obfuscated-mailto="2Q91P4rbAQAJ" rel="nofollow" onmousedown="this.href=&#39;javascript:&#39;;return true;" onclick="this.href=&#39;javascript:&#39;;return true;">glorp-group...@googlegroups.com.
To post to this group, send email to <a href="javascript:" target="_blank" gdf-obfuscated-mailto="2Q91P4rbAQAJ" rel="nofollow" onmousedown="this.href=&#39;javascript:&#39;;return true;" onclick="this.href=&#39;javascript:&#39;;return true;">glorp...@....
Visit this group at <a href="https://groups.google.com/group/glorp-group" target="_blank" rel="nofollow" onmousedown="this.href=&#39;https://groups.google.com/group/glorp-group&#39;;return true;" onclick="this.href=&#39;https://groups.google.com/group/glorp-group&#39;;return true;">https://groups.google.com/group/glorp-group.
For more options, visit <a href="https://groups.google.com/d/optout" target="_blank" rel="nofollow" onmousedown="this.href=&#39;https://groups.google.com/d/optout&#39;;return true;" onclick="this.href=&#39;https://groups.google.com/d/optout&#39;;return true;">https://groups.google.com/d/optout.

sorry, asOuterJoin, not beOuterJoin....

Am Samstag, 21. Januar 2017 05:51:33 UTC+1 schrieb jtuchel:

Alan,

terrific. fanstastic. thank you. As always, I'm blown away.

The whole trick is this:

query :=
        (SimpleQuery returningManyOf: Vehicle where: [:ea | ea color = 'green'])
            orderBy: [:ea | ea whatever descending];
            alsoFetch: [:ea | ea wheels];
            alsoFetch: [:ea | ea engine beOuterJoin];
            alsoFetch: [:ea| ea engine controller beOuterJoin] .

That's really all. Can't believe I didn't try.

Joachim

Am Freitag, 20. Januar 2017 22:00:50 UTC+1 schrieb alan.knight:

You can send asOuterJoin within the query block of each alsoFetch:  Or to the block once it's been converted to an expression (or something similar, details left as an exercise for the reader).

 

On Fri, Jan 20, 2017 at 12:52 PM jtuchel <[hidden email]> wrote:

I'd like to fly another round of performance tuning on some central parts of my application. So I'd like to do things like this:


query :=
        (SimpleQuery returningManyOf: Vehicle where: [:ea | ea color = 'green'])
            orderBy: [:ea | ea whatever descending];
            alsoFetch: [:ea | ea wheels];
            alsoFetch: [:ea | ea engine];
            alsoFetch: [:ea| ea engine controller] .

Unfortunatley, this query only finds green verhicles that have wheels as well as an engine and whose engine is equipped with a controller. If a vehicle has no engine, it will not be in the result set.

What I'd hope to get is a list of *all* green vehicles,
* with all their wheels (if they have some),
* with their engine (if they have one)
* and their engine's controller (if there is one).

Or, put differently: I want to find

* all green verhicles that have neither wheels nor an engine
* all green verhicles that have any number of wheels, together with their wheels
* all green vehicles with or without wheels, with an engine
* all green vehicles with or without wheels with an engine and a controller


So I think what I am asking for is a way to express this query as an outer join. Neither Query nor AbstractReadQuery understand #beOuterJoin, unfortunately. And to be honest, I am not even sure if an outer join would work for this (my SQL know how is limited).

Does Glorp have any tricks in stock for my use case? I want to avoid ending up with three additional queries for each green vehicle to get their wheels, engine and their engine's controller. So even if I have to collect a list of ids in one statement and do one select for all engines of cars with an engine by id, this would still be a huge speedup compared to following the proxies with single selects for each vehicle (in the worst case 3 SQL statements once the vehicle itself is loaded, so for 1000 vehicels, resolving proxies individually means 3001 SQL queries, plus one for each car with a controller...).

Any ideas or hints?

Joachim

--
You received this message because you are subscribed to the Google Groups "glorp-group" group.
To unsubscribe from this group and stop receiving emails from it, send an email to glorp-group...@googlegroups.com.
To post to this group, send email to [hidden email].
Visit this group at <a href="https://groups.google.com/group/glorp-group" rel="nofollow" target="_blank" onmousedown="this.href=&#39;https://groups.google.com/group/glorp-group&#39;;return true;" onclick="this.href=&#39;https://groups.google.com/group/glorp-group&#39;;return true;">https://groups.google.com/group/glorp-group.
For more options, visit <a href="https://groups.google.com/d/optout" rel="nofollow" target="_blank" onmousedown="this.href=&#39;https://groups.google.com/d/optout&#39;;return true;" onclick="this.href=&#39;https://groups.google.com/d/optout&#39;;return true;">https://groups.google.com/d/optout.