查询Dsl SQL - 左联接子查询

Question

我正在使用，我想进行子查询。这是普通查询QueryDsl SQLleft joinSQLSELECT  usr.memberId,  payoutsBbf.totalPytsFROM  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 SQLusers我如何处理零件LEFT JOIN x ON如何处理部件中的子查询别名SELECT payoutBbf.totalPyts

Answer 1

这就是我会怎么做的：

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

Answer 2

我认为这对你有用。虽然这有点笨拙：

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

请注意使用定义为 的别名来指定连接列。另请注意使用 在部分中指定子列payoutsBbfExpressions.path()select()