2

I am using QueryDsl SQL and I want to left join a subquery. Here is the query in plain SQL

SELECT
  usr.memberId,
  payoutsBbf.totalPyts
FROM
  users usr
  LEFT JOIN
    (SELECT
       pyt.member_id   AS mmb_id,
       SUM(pyt.amount) AS totalPyts
  FROM
    payout pyt
  WHERE
    pyt.payoutPeriod < '2018-01-01'
    GROUP BY pyt.member_id) AS payoutsBbf ON usr.id = payoutsBbf.mmb_id

I wish to write it in QueryDsl SQL and is almost important that the subquery is left joined to the main table users coz this is just a snippet of the whole complex query.

  1. How do I deal with the LEFT JOIN x ON part

  2. How do I deal with the subquery alias in the SELECT payoutBbf.totalPyts part

Kihats
  • 3,326
  • 5
  • 31
  • 46
  • `.leftJoin` http://www.querydsl.com/static/querydsl/latest/reference/html/ch02.html#d0e302 – spencer7593 Apr 08 '19 at 19:21
  • The challenge here is I am `leftJoin-ing` a `QUsers` to a subquery. With the `Q`-class I can use dot notation to wirte `.leftJoin(subquery).on(qusers.id.eq(??)` but I hit a snag on the subquery `on` part – Kihats Apr 08 '19 at 19:31
  • Similar question's answer: https://stackoverflow.com/a/67254869/3308908 – GauravRatnawat Apr 25 '21 at 15:14

2 Answers2

5

this is how I would do it:

final StringPath payoutsBbf = stringPath("payoutsBbf");
final String mmbId = "mmb_id";
final String totalPyts = "totalPyts";
sqlQueryFactory.select(users.memberId, stringPath(payoutsBbf, totalPyts))
    .from(users).leftJoin(
        sqlQueryFactory.select(payout.member_id.as(mmbId), member_id.amount.sum().as(totalPyts))
            .from(payout).where(payout.payoutPeriod.lt("2018-01-01")).groupBy(payout.member_id),
        payoutsBbf
    ).on(users.id.eq(stringPath(payoutsBbf, mmbId))).fetch();
raven
  • 775
  • 6
  • 22
  • That was really simple. You are a life saver. Thanks – Kihats Jan 11 '21 at 11:03
  • For those wondering how the `StringPath` was created: `Expressions.stringPath("payoutBbf");` `Expressions.stringPath(payoutBbf, totalPyts)` ... and also since the `users.id` is a `NumberPath` use this: `Expressions.numberPath(Long.class, payoutBbf, mmbId)` – Kihats Jan 11 '21 at 11:11
  • How is this even compiling? Is this still possible in querydsl 5? Putting the subquery as an argument in the leftJoin with a tuple ends up with `Cannot resolve method 'leftJoin(com.querydsl.jpa.JPQLQuery, com.querydsl.core.types.dsl.StringPath)'` – nailer_boxer Feb 18 '22 at 18:24
  • @nailer_boxer I haven't been working with querydsl for a while so it would take a lot of time for me to test it; but from your error it seems that you are using querydsl with JPA; I didn't use JPA. – raven Feb 19 '22 at 08:11
  • @raven, yes I use it with JPA so I guess that's the problem. Anyway, thank you :). I found another solution which suits me. I will post it maybe tomorrow if I have time in case someone else struggles with that. – nailer_boxer Feb 20 '22 at 01:55
4

I think this will work for you. It's a bit hacky though:

SQLQueryFactory sqlqf; // Should be @Autowired

QUsers qusers = new QUsers();
QPayouts qpayouts = new QPayouts();

Expression<Long> memberId = ExpressionUtils.as(qpayouts.memberId, "mmb_id");
Expression<Double> totalPayouts = ExpressionUtils.as(qpayouts.amount.sum(), "totalPayouts");

SQLQuery<Tuple> payoutsBbf = SQLExpressions.select(memberId, totalPayouts)
            .from(qpayouts)                
            .where(qpayouts.payoutPeriod.lt("2018-01-01")) // Use date object
            .groupBy(qpayouts.memberId);

final SimplePath<? extends SQLQuery> payoutsBBfPath = Expressions.path(payoutsBBfPath.getClass(), "payoutsBbf");

List<Tuple> fetch = sqlqf.select(
        qusers.memberId,                
        Expressions.path(payoutsBbf.getClass(), new PathMetadata(payoutsBBfPath, "totalPayouts", PathType.PROPERTY))
    )
    .from(qusers)
    .leftJoin(payoutsBbf, payoutsBBfPath)
    .addJoinFlag(" on payoutsBbf.mmb_id = users.id", JoinFlag.Position.BEFORE_CONDITION)
    .fetch();

Note the usage of JoinFlag to specify the join column, using the alias defined as payoutsBbf. Also note the use of Expressions.path() to specify sub-columns in the select() section

maxuville
  • 521
  • 1
  • 5
  • 19