QueryDsl SQL - 左加入子查询

问题描述

我正在使用 QueryDsl SQL 并且我想 left join 一个子查询.这是普通 SQL

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

我希望用 QueryDsl SQL 编写它并且将子查询保留连接到主表 users 几乎很重要，因为这只是整个复杂的一个片段查询.

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. 如何处理LEFT JOIN x ON部分

如何处理SELECT payoutBbf.totalPyts部分中的子查询别名

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

推荐答案

我会这样做:

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();

这篇关于QueryDsl SQL - 左加入子查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！