Querydsl, subquery in SELECT

Question

I'm running the following SQL as a native query but I would like to know if there is a way to run it in JPAQuery in order to use tuples or class instantiation.

SELECT a.*, 
      (SELECT exists (SELECT 1 FROM Table b WHERE b.a_code = a.code AND b.other =  ?)) AS bloquant 
FROM Table a

For precision, I'm using aliases not QTypes.

Do you mean: `SELECT a.* FROM tablea a where exists (SELECT 1 FROM tableb b WHERE b.a_code= a.b_code AND b.other = ?)` Which could be written as `select a.* from table_a a where a_code in ( select b.a_code from tableb b where a.a_code = b.a_code and other = ?)` — KarlP, Oct 11 '16 at 11:17
@KarlP No, they clearly mean a subquery in the SELECT part, which I'm also wondering how to write. — Tobia, May 12 '20 at 07:38
@Tobia Thanks. No I don't think a select in the select clause is possible using JPQL. — KarlP, May 12 '20 at 23:35
@KarlP Yes, it's possible. I just wrote it in JPQL / HQL and it works. In fact, I added it to my QueryDSL query using `Expressions.template()` but it's a hack, so if QueryDSL supported it natively, it would be cleaner. — Tobia, May 14 '20 at 06:41
@KarlP Just this: `Expressions.stringTemplate("(select foo from Something s where s.bar = {0}.baz)", someEntity)).as("myAlias")` where someEntity takes the place of the {0} placeholder in the JPQL template. There are other `*Template` for different column types (this one outputs a String) — Tobia, Jul 05 '20 at 20:33

KarlP · Answer 1 · 2020-05-13T23:14:56.647

I don't think a subquery in a select clause is supported by JPQL and JPA judging by this:

JPQL LangRef: https://docs.oracle.com/html/E13946_04/ejb3_langref.html#ejb3_langref_select_clause

The SELECT clause has the following syntax:

select_clause ::= SELECT [DISTINCT] select_expression {, select_expression}*

select_expression ::= single_valued_path_expression | aggregate_expression | 
identification_variable | OBJECT(identification_variable) | constructor_expression

constructor_expression ::= NEW constructor_name ( constructor_item {, 
constructor_item}* )

constructor_item ::= single_valued_path_expression | aggregate_expression

aggregate_expression ::= { AVG | MAX | MIN | SUM } ([DISTINCT] 
state_field_path_expression) | COUNT ([DISTINCT] identification_variable | 
state_field_path_expression | single_valued_association_path_expression)

As a workaround, you could do a native query or an entity on top of a view. To keep things reasonably clean you could create a view with only the subqueries (and a primary key) and do a lazy one-to-one mapping between the entities.

Note that (complicated) predicates may not be pushed into the view-query efficiently, and typically not very efficiently at all when doing subqueries in the select clause.

score -1 · Answer 2 · answered Jun 22 '17 at 14:02

If your correct query is:

SELECT *
FROM tablea
WHERE EXISTS(SELECT 1 FROM tableb WHERE tableb.a_code=tablea.code and tableb=$PARAM_VALUE);

Then you can get the correct SQL expression doing this:

QTableA qTableA = new QTableA("tablea");
QTableB qTableB = new QTableB("tableb");

// Subquery creation
SQLQuery subquery = SQLExpressions.selectOne()
    .from(qTableB)
    .where(qTableB.a_code.eq(qTableA.code).and(qTableB.other.eq(PARAM_VALUE)));
return subquery.exists();

SQLQuery query = new SQLQuery();
query.setUseLiterals(true);
query
    .select(SQLExpressions.countAll)
    .from(qTableA)
    .where(subquery.exists());
return query.getSQL().getSQL();

OP specifically asked for a subquery in the SELECT part, and you answered with a subquery in the WHERE part. — Tobia, May 12 '20 at 07:37

Querydsl, subquery in SELECT

2 Answers2