alsoFetch: with OUTER JOIN?

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
4 messages Options
Reply | Threaded
Open this post in threaded view
|

alsoFetch: with OUTER JOIN?

jtuchel
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 [hidden email].
To post to this group, send email to [hidden email].
Visit this group at https://groups.google.com/group/glorp-group.
For more options, visit https://groups.google.com/d/optout.
Reply | Threaded
Open this post in threaded view
|

Re: alsoFetch: with OUTER JOIN?

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 [hidden email].
To post to this group, send email to [hidden email].
Visit this group at https://groups.google.com/group/glorp-group.
For more options, visit https://groups.google.com/d/optout.

--
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 [hidden email].
To post to this group, send email to [hidden email].
Visit this group at https://groups.google.com/group/glorp-group.
For more options, visit https://groups.google.com/d/optout.
Reply | Threaded
Open this post in threaded view
|

Re: alsoFetch: with OUTER JOIN?

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 <<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.

--
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 [hidden email].
To post to this group, send email to [hidden email].
Visit this group at https://groups.google.com/group/glorp-group.
For more options, visit https://groups.google.com/d/optout.
Reply | Threaded
Open this post in threaded view
|

Re: alsoFetch: with OUTER JOIN?

jtuchel
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.

--
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 [hidden email].
To post to this group, send email to [hidden email].
Visit this group at https://groups.google.com/group/glorp-group.
For more options, visit https://groups.google.com/d/optout.