New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Improve SQLSubQuery signature #580

Closed
timowest opened this Issue Dec 3, 2013 · 4 comments

Comments

Projects
None yet
2 participants
@timowest
Member

timowest commented Dec 3, 2013

SQLSubQuery.union should return a CollectionExpression instance which can be used in

SimpleExpression.in(CollectionExpression)

timowest added a commit that referenced this issue Dec 3, 2013

timowest added a commit that referenced this issue Dec 3, 2013

@timowest

This comment has been minimized.

Show comment
Hide comment
@timowest

timowest Dec 12, 2013

Member

Released in 3.3.0.BETA2

Member

timowest commented Dec 12, 2013

Released in 3.3.0.BETA2

@timowest timowest closed this Dec 12, 2013

@magnuskvalheim

This comment has been minimized.

Show comment
Hide comment
@magnuskvalheim

magnuskvalheim Dec 12, 2013

Hi Timo. Very happy to see this picked up!

Was testing beta2 and looks like it's missing a parenthesis - at least in my testing...
Postgresql 9.3.1

SMovella smovella = SMovella.movella;
SCoAuthor sco = SCoAuthor.coAuthor;

ListSubQuery<Long> subMovellaByCoAuthorId = new SQLSubQuery()
    .from(sco)
    .where(sco.userId.eq(userId))
    .list(sco.userId);

ListSubQuery<Long> subMovellaByAuthorId = new SQLSubQuery()
    .from(smovella)
    .where(smovella.userId.eq(userId))
    .list(smovella.userId);             

List<Movella> mList = new JPASQLQuery(getEm(), PostgresTemplates.builder().printSchema().build())
    .from(smovella)
    .where(smovella.userId.in(new SQLSubQuery().union(subMovellaByAuthorId, subMovellaByCoAuthorId)))
    .list(QMovella.movella);

Generates SQL

select movella.*
    from movella.movella movella
    where movella.user_id in (select movella.user_id
    from movella.movella movella
    where movella.user_id = $1)
    union
    (select co_author.user_id
    from movella.co_author co_author
    where co_author.user_id = $2)

This is failing with "each UNION query must have the same number of columns at character 167"

The query can execute if the union is enclosed in parenthesis.

select movella.*
    from movella.movella movella
    where movella.user_id in ((select movella.user_id
    from movella.movella movella
    where movella.user_id = $1)
    union
    (select co_author.user_id
    from movella.co_author co_author
    where co_author.user_id = $2))

Another learning I discovered was that
new JPASQLQuery(getEm(), new PostgresTemplates()) was not generating the qualified schema.table notation.

I had to use
new JPASQLQuery(getEm(), PostgresTemplates.builder().printSchema().build())
which I discovered by looking through sources.
Should the "schema.table" not be default with postgres?

best
Magnus

Hi Timo. Very happy to see this picked up!

Was testing beta2 and looks like it's missing a parenthesis - at least in my testing...
Postgresql 9.3.1

SMovella smovella = SMovella.movella;
SCoAuthor sco = SCoAuthor.coAuthor;

ListSubQuery<Long> subMovellaByCoAuthorId = new SQLSubQuery()
    .from(sco)
    .where(sco.userId.eq(userId))
    .list(sco.userId);

ListSubQuery<Long> subMovellaByAuthorId = new SQLSubQuery()
    .from(smovella)
    .where(smovella.userId.eq(userId))
    .list(smovella.userId);             

List<Movella> mList = new JPASQLQuery(getEm(), PostgresTemplates.builder().printSchema().build())
    .from(smovella)
    .where(smovella.userId.in(new SQLSubQuery().union(subMovellaByAuthorId, subMovellaByCoAuthorId)))
    .list(QMovella.movella);

Generates SQL

select movella.*
    from movella.movella movella
    where movella.user_id in (select movella.user_id
    from movella.movella movella
    where movella.user_id = $1)
    union
    (select co_author.user_id
    from movella.co_author co_author
    where co_author.user_id = $2)

This is failing with "each UNION query must have the same number of columns at character 167"

The query can execute if the union is enclosed in parenthesis.

select movella.*
    from movella.movella movella
    where movella.user_id in ((select movella.user_id
    from movella.movella movella
    where movella.user_id = $1)
    union
    (select co_author.user_id
    from movella.co_author co_author
    where co_author.user_id = $2))

Another learning I discovered was that
new JPASQLQuery(getEm(), new PostgresTemplates()) was not generating the qualified schema.table notation.

I had to use
new JPASQLQuery(getEm(), PostgresTemplates.builder().printSchema().build())
which I discovered by looking through sources.
Should the "schema.table" not be default with postgres?

best
Magnus

@timowest

This comment has been minimized.

Show comment
Hide comment
@timowest

timowest Dec 12, 2013

Member

Could you create new issues for these cases? They are not directly related to this one, since this one is more about the Querydsl signature, not the code generation.

Member

timowest commented Dec 12, 2013

Could you create new issues for these cases? They are not directly related to this one, since this one is more about the Querydsl signature, not the code generation.

@magnuskvalheim

This comment has been minimized.

Show comment
Hide comment
@magnuskvalheim

magnuskvalheim Dec 12, 2013

Yes np, sorry about that.

I've created
Postgresql - "in with union" is missing parenthesis - #593

and
Postgresql - qualified "schema.table" should be default - #594

Yes np, sorry about that.

I've created
Postgresql - "in with union" is missing parenthesis - #593

and
Postgresql - qualified "schema.table" should be default - #594

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