如何使用子查询生成正确的查询生成器

Question

我有一个带有付款的表，我需要获得所有表列和四个自定义列。我当前的工作查询：

$this->createQueryBuilder('payments')
->select('DATE_FORMAT(payments.createdAt, '%Y-%m') as date_ym, payments')
->addSelect('(SELECT SUM(ps1.summ) FROM AppEntityPayments ps1 WHERE ps1.isPayed = 1 AND ps1.type = 1 AND ps1.user = :user AND DATE_FORMAT(ps1.createdAt, '%Y-%m') = date_ym) as pay_in')
->addSelect('(SELECT COUNT(ps3.id) FROM AppEntityPayments ps3 WHERE ps3.isPayed = 1 AND ps3.type = 1 AND ps3.user = :user AND DATE_FORMAT(ps3.createdAt, '%Y-%m') = date_ym) as pay_in_count')
->addSelect('(SELECT SUM(ps2.summ) FROM AppEntityPayments ps2 WHERE ps2.isPayed = 1 AND ps2.type != 1 AND ps2.user = :user AND DATE_FORMAT(ps2.createdAt, '%Y-%m') = date_ym) as pay_out')
->addSelect('(SELECT COUNT(ps4.id) FROM AppEntityPayments ps4 WHERE ps4.isPayed = 1 AND ps4.type != 1 AND ps4.user = :user AND DATE_FORMAT(ps4.createdAt, '%Y-%m') = date_ym) as pay_out_count')
->where('payments.user = :user')
->setParameter('user', $user)
->andWhere('payments.isPayed = 1')
->andWhere('payments.createdAt >= :days')
->setParameter('days', $days->format('Y-m-d'))
->orderBy('date_ym', 'DESC')
->getQuery()->getResult();

在控制器中我做：

$payments = $em->getRepository(Payments::class)->myFunc($user, 180);
$paymentsTable = [];
foreach ($payments as $payment) {
$date = $payment['date_ym'];
/** @var PaySchet $pay */
$pay = $payment[0];
$paymentsTable[$date]['pay_in'] = $payment['pay_in'];
$paymentsTable[$date]['pay_in_count'] = $payment['pay_in_count'];
$paymentsTable[$date]['pay_out'] = $payment['pay_out'];
$paymentsTable[$date]['pay_out_count'] = $payment['pay_out_count'];
$paymentsTable[$date]['data'][$pay->getType()][] = $pay;
}

我得到以下结果：

array:3 [▼
"2020-03" => array:5 [▼
"pay_in" => "15"
"pay_in_count" => "1"
"pay_out" => "230"
"pay_out_count" => "1"
"data" => array:1 [▼
0 => AppEntityPayments {#2288 ▶}
]
]
"2020-01" => array:5 [▼
"pay_in" => "2620"
"pay_in_count" => "5"
"pay_out" => "13732"
"pay_out_count" => "3"
"data" => array:3 [▼
0 => AppEntityPayments {#2267 ▶}
1 => AppEntityPayments {#2257 ▶}
2 => AppEntityPayments {#2251 ▶}
]
]
"2019-12" => array:5 [▶]
]

这是正确的输出，但我如何改进我的DQL查询？我不喜欢现在的样子。

Answer 1

您可以创建多个查询生成器，并在主查询生成器中使用它们。只需注意，您必须在所有实体中使用唯一的实体别名。您可以使用这些别名引用其他查询生成器中的实体。

/** @val $em EntityManager */
$query1 = $em->createQueryBuilder()->from(Payments::class, 'ps1')->select('')->getDql();
$query2 = $em->createQueryBuilder()->from(Payments::class, 'ps2')->select('')->getDql();
$query3 = $em->createQueryBuilder()->from(Payments::class, 'ps3')->select('')->getDql();
$query4 = $em->createQueryBuilder()->from(Payments::class, 'ps4')->select('')->getDql();
// All value binds need to be on the main query builder
$query = $qb->select('DATE_FORMAT(payments.createdAt, '%Y-%m') as date_ym, payments')
->addSelect($query1)
->addSelect($query2)
->addSelect($query3)
->addSelect($query4)
->from(Payments::class, 'payments')
->where('payments.user = :user')
->setParameter('user', $user)
->andWhere('payments.isPayed = 1')
->andWhere('payments.createdAt >= :days')
->setParameter('days', $days->format('Y-m-d'))
->orderBy('date_ym', 'DESC')
->getQuery()
->getResult();