Performing union prevents common table expressions from appearing in query. #798

Closed
robertandrewbain opened this Issue Jun 10, 2014 · 7 comments

Comments

Projects
None yet
3 participants
@robertandrewbain
Member

robertandrewbain commented Jun 10, 2014

SQLQuery query = query();
query.with(fooPathAlias, fooSubqueryExpression);
query.union(barListSubQuery, bazListSubQuery);
query.list();

The SQL to manifest does not contain the common table expression added on line 2 but instead has only the select statement for the union which, for obvious reasons, won't compile.

@robertandrewbain

This comment has been minimized.

Show comment
Hide comment
@robertandrewbain

robertandrewbain Jun 12, 2014

Member

I built commit b0b874e and can confirm that the fix works on a complex query. Thanks Timo, you implemented that very quickly!

Member

robertandrewbain commented Jun 12, 2014

I built commit b0b874e and can confirm that the fix works on a complex query. Thanks Timo, you implemented that very quickly!

@timowest

This comment has been minimized.

Show comment
Hide comment
@timowest

timowest Jun 12, 2014

Member

Thanks for testing, I'll (re)close it when it's released.

Member

timowest commented Jun 12, 2014

Thanks for testing, I'll (re)close it when it's released.

@timowest timowest reopened this Jun 12, 2014

@robertandrewbain

This comment has been minimized.

Show comment
Hide comment
@robertandrewbain

robertandrewbain Jun 12, 2014

Member

Apologies, the fact that the "close" button was available to me as the
issue creator led me to assume that it was my role to close it, although I
did consider that this didn't make complete sense, given that it has not
made the release cycle.

My team have been using querydsl for a month or so and as we continue to
get to grips with the api, are enjoying using it more and more.
Incidentally, thanks for your quick turnaround on #798, that was a real
show-stopper for our project. I have a couple of questions that I can't
seem to find an answer to from the documentation or associated google
groups. I appreciate that you offer support for the product for a fee, so
please don't feel awkward to send me on my way, I can see that you're a
busy man.

From looking at the API, I must assume that subqueries in the select clause
are unsupported?

I can't see a method of (deliberately) performing a Cartesian join. In ANSI
SQL, you would perform your join conditions, E.G.

FROM employee e INNER JOIN car c ON e.id = c.employee.id,
construct_to_cartesian_join_to

I was also looking to see if I could in some way manipulate the serialsed
query. I'm connecting to a third party Oracle database and need to provide
optimizer hints. Can you think of anything?

Another thought that came to mind is the way select statements for unions
are handled. In ANSI SQL, I believe you don't have to alias columns in each
section of the union - it just cares about data type. The current querydsl
api makes it a little painful, as you have to create a PathBuilder for each
column and use ".as" on each expression in each of the selects. Ordering
was tough too - I found myself delving into the api and creating my own
OrderSpecifier instances - it would be nice if orderBy would accept a Path.

Thanks for all your hard work on querydsl, I'm very impressed with it so
far!

Kind regards

Rob

Member

robertandrewbain commented Jun 12, 2014

Apologies, the fact that the "close" button was available to me as the
issue creator led me to assume that it was my role to close it, although I
did consider that this didn't make complete sense, given that it has not
made the release cycle.

My team have been using querydsl for a month or so and as we continue to
get to grips with the api, are enjoying using it more and more.
Incidentally, thanks for your quick turnaround on #798, that was a real
show-stopper for our project. I have a couple of questions that I can't
seem to find an answer to from the documentation or associated google
groups. I appreciate that you offer support for the product for a fee, so
please don't feel awkward to send me on my way, I can see that you're a
busy man.

From looking at the API, I must assume that subqueries in the select clause
are unsupported?

I can't see a method of (deliberately) performing a Cartesian join. In ANSI
SQL, you would perform your join conditions, E.G.

FROM employee e INNER JOIN car c ON e.id = c.employee.id,
construct_to_cartesian_join_to

I was also looking to see if I could in some way manipulate the serialsed
query. I'm connecting to a third party Oracle database and need to provide
optimizer hints. Can you think of anything?

Another thought that came to mind is the way select statements for unions
are handled. In ANSI SQL, I believe you don't have to alias columns in each
section of the union - it just cares about data type. The current querydsl
api makes it a little painful, as you have to create a PathBuilder for each
column and use ".as" on each expression in each of the selects. Ordering
was tough too - I found myself delving into the api and creating my own
OrderSpecifier instances - it would be nice if orderBy would accept a Path.

Thanks for all your hard work on querydsl, I'm very impressed with it so
far!

Kind regards

Rob

@Shredder121

This comment has been minimized.

Show comment
Hide comment
@Shredder121

Shredder121 Jun 12, 2014

Member

I don't know how dynamic you are using the API, but as for the Orderspecifier instance creation, regular usage is not that hard.
when you have your path, e.g:
NumberPath number, you can construct your orderspecifier like so:
query().orderBy(number.asc())

Member

Shredder121 commented Jun 12, 2014

I don't know how dynamic you are using the API, but as for the Orderspecifier instance creation, regular usage is not that hard.
when you have your path, e.g:
NumberPath number, you can construct your orderspecifier like so:
query().orderBy(number.asc())

@timowest

This comment has been minimized.

Show comment
Hide comment
@timowest

timowest Jun 13, 2014

Member

From looking at the API, I must assume that subqueries in the select clause
are unsupported?

Should be supported. Did you run into any walls?

FROM employee e INNER JOIN car c ON e.id = c.employee.id, construct_to_cartesian_join_to

What could construct_to_cartesian_join_to be? Do you miss join types?

I was also looking to see if I could in some way manipulate the serialsed
query. I'm connecting to a third party Oracle database and need to provide
optimizer hints. Can you think of anything?

Query flags are the way to do here: query.addFlag(position, string)

The current querydsl api makes it a little painful, as you have to create a PathBuilder for each
column and use ".as" on each expression in each of the selects.

Feel free to suggest syntactical improvements.

Ordering was tough too - I found myself delving into the api and creating my own
OrderSpecifier instances - it would be nice if orderBy would accept a Path.

@Shredder121 is right, for typed instances it works fine. Do you have a dynamic query population use case?

Member

timowest commented Jun 13, 2014

From looking at the API, I must assume that subqueries in the select clause
are unsupported?

Should be supported. Did you run into any walls?

FROM employee e INNER JOIN car c ON e.id = c.employee.id, construct_to_cartesian_join_to

What could construct_to_cartesian_join_to be? Do you miss join types?

I was also looking to see if I could in some way manipulate the serialsed
query. I'm connecting to a third party Oracle database and need to provide
optimizer hints. Can you think of anything?

Query flags are the way to do here: query.addFlag(position, string)

The current querydsl api makes it a little painful, as you have to create a PathBuilder for each
column and use ".as" on each expression in each of the selects.

Feel free to suggest syntactical improvements.

Ordering was tough too - I found myself delving into the api and creating my own
OrderSpecifier instances - it would be nice if orderBy would accept a Path.

@Shredder121 is right, for typed instances it works fine. Do you have a dynamic query population use case?

@robertandrewbain

This comment has been minimized.

Show comment
Hide comment
@robertandrewbain

robertandrewbain Jun 13, 2014

Member

I don't know how dynamic you are using the API, but as for the Orderspecifier instance creation, regular usage is not that hard.

Thanks @Shredder121, based on your example, I've got that cleaned up.

Should be supported. Did you run into any walls?

Apparently only my inexperience with the api and a lack of examples. In hindsight, this is very straightforwards. How did I miss this???

What could construct_to_cartesian_join_to be? Do you miss join types?

I was unaware of the "CROSS JOIN" type. Coming from a non-ANSI Oracle background, previously I would list a table in the FROM clause and simply not apply any join conditions or filtering.

Query flags are the way to do here: query.addFlag(position, string)

Thanks, this is exactly what I am looking for.

Feel free to suggest syntactical improvements.

Based on this conversation, I think it would be wise for me to get a few more api miles beneath my feet before passing further comment. My team and I are committed to using querydsl for all of our reporting needs, so as and when we do have any suggestions, we'll be sure to share them.

@timowest Thanks again for your quick response. Filling in these small gaps has really enhanced our use of the api.

Member

robertandrewbain commented Jun 13, 2014

I don't know how dynamic you are using the API, but as for the Orderspecifier instance creation, regular usage is not that hard.

Thanks @Shredder121, based on your example, I've got that cleaned up.

Should be supported. Did you run into any walls?

Apparently only my inexperience with the api and a lack of examples. In hindsight, this is very straightforwards. How did I miss this???

What could construct_to_cartesian_join_to be? Do you miss join types?

I was unaware of the "CROSS JOIN" type. Coming from a non-ANSI Oracle background, previously I would list a table in the FROM clause and simply not apply any join conditions or filtering.

Query flags are the way to do here: query.addFlag(position, string)

Thanks, this is exactly what I am looking for.

Feel free to suggest syntactical improvements.

Based on this conversation, I think it would be wise for me to get a few more api miles beneath my feet before passing further comment. My team and I are committed to using querydsl for all of our reporting needs, so as and when we do have any suggestions, we'll be sure to share them.

@timowest Thanks again for your quick response. Filling in these small gaps has really enhanced our use of the api.

@timowest timowest added this to the 3.4.1 milestone Jun 13, 2014

@timowest timowest modified the milestone: 3.4.1 Jun 25, 2014

@timowest

This comment has been minimized.

Show comment
Hide comment
@timowest

timowest Jun 29, 2014

Member

Released in 3.4.1

Member

timowest commented Jun 29, 2014

Released in 3.4.1

@timowest timowest closed this Jun 29, 2014

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment