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

Support for multi-column in-clauses #313

Closed
ssaarela opened this Issue Dec 13, 2012 · 4 comments

Comments

Projects
None yet
2 participants
@ssaarela
Contributor

ssaarela commented Dec 13, 2012

When dealing with multi column primary keys in-clause becomes

select ... from x where (x.keyRefPart1, x.keyRefPart2) in (select y.keyPart1, y.keyPart2 from y)

While this can be achieved using custom extension, it would be nice to have "official" support - or is there one already? ArrayPath?

Here's an example, how this works using custom template-extension:

SimpleExpression<Object[]>  idExpr = SimpleTemplate.create(Object[].class, "({0}, {1})", qX.keyRefPart1, qX.keyRefPart2);

qry.where(idExpr.in(subQuery.list(qY.keyPart1, qY.keyPart2)));

There is one caveat to be aware of: how to do subQuery.list differs in different DB's. In H2 one needs to use similar expression in select-subquery: where (x, y) in (select (x2, y2) from...). Can this kind of difference be resolved with SQLTemplates? Something like MultiColumnKeyExpression<Object[]> that has different serialization in select and where part of the query.

A nice helper for MultiColumnExpressions could be added to into PrimaryKey and ForeignKey - something similar to getLocalColumns() but returning an Expression instead of List. Having serialization of this kind of MultiColumnExpression take single-column expression as an special case into account (serialize without parenthesis), this would probably be quite usable shortcut for single or multi-column comparisons.

@timowest

This comment has been minimized.

Show comment
Hide comment
@timowest

timowest Dec 13, 2012

Member

While this can be achieved using custom extension, it would be nice to have "official" support - or is there one already? ArrayPath?

The following static method in ExpressionUtils does it

public static <T> Expression<T> list(Class<T> clazz, Expression<?>... exprs)

But I will put it into the Expressions class as well, which is more part of the public API

Can this kind of difference be resolved with SQLTemplates?

Should be possible.

Having serialization of this kind of MultiColumnExpression take single-column expression as an special case into account (serialize without parenthesis), this would probably be quite usable shortcut for single or multi-column comparisons.

Ok.

Member

timowest commented Dec 13, 2012

While this can be achieved using custom extension, it would be nice to have "official" support - or is there one already? ArrayPath?

The following static method in ExpressionUtils does it

public static <T> Expression<T> list(Class<T> clazz, Expression<?>... exprs)

But I will put it into the Expressions class as well, which is more part of the public API

Can this kind of difference be resolved with SQLTemplates?

Should be possible.

Having serialization of this kind of MultiColumnExpression take single-column expression as an special case into account (serialize without parenthesis), this would probably be quite usable shortcut for single or multi-column comparisons.

Ok.

@ssaarela

This comment has been minimized.

Show comment
Hide comment
@ssaarela

ssaarela Dec 13, 2012

Contributor

...or maybe PrimaryKey and ForeignKey could them selves implement this kind of (multi) column expression? That would be most handy.

There seems to be quite much variance in how this kind of statements are supported. At least when tested with http://sqlfiddle.com/. In some DB's you can even use this kind of comparisons: (a,b) = (c,d) in joins and where.

Having Querydsl solve those mismatches (e.g. converting previous example into (a=c and b=d) in Oracle) would be great! - And I guess that requires support at SQLTemplates level (i.e. Querydsl internal) - ?

Contributor

ssaarela commented Dec 13, 2012

...or maybe PrimaryKey and ForeignKey could them selves implement this kind of (multi) column expression? That would be most handy.

There seems to be quite much variance in how this kind of statements are supported. At least when tested with http://sqlfiddle.com/. In some DB's you can even use this kind of comparisons: (a,b) = (c,d) in joins and where.

Having Querydsl solve those mismatches (e.g. converting previous example into (a=c and b=d) in Oracle) would be great! - And I guess that requires support at SQLTemplates level (i.e. Querydsl internal) - ?

timowest added a commit that referenced this issue Dec 14, 2012

@timowest

This comment has been minimized.

Show comment
Hide comment
@timowest

timowest Dec 17, 2012

Member

H2 is the only one which works with the alternatve syntax. I don't know yet find a way to make it work for CUBRID, Derby, HSQLDB and SQLite.

But it works for MySQL, PostgreSQL and Oracle.

Member

timowest commented Dec 17, 2012

H2 is the only one which works with the alternatve syntax. I don't know yet find a way to make it work for CUBRID, Derby, HSQLDB and SQLite.

But it works for MySQL, PostgreSQL and Oracle.

@timowest

This comment has been minimized.

Show comment
Hide comment
@timowest

timowest Jan 13, 2013

Member

Released in 3.0.0.BETA1

Member

timowest commented Jan 13, 2013

Released in 3.0.0.BETA1

@timowest timowest closed this Jan 13, 2013

@timowest timowest added this to the 3.0.0 milestone Apr 14, 2014

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