php - 教义试图从三个具有左连接并按两个表分组的表中获取结果是否正确?
问题描述
我正在为组查询而苦苦挣扎,有人可以帮忙吗?
教义/PHP/Mysql
我需要从第一个表 (RepsProductsToSubStock) 中获取库存产品的数量,按每个代表 (Reps) 的产品名称 (Products) 显示。我想出了简单的查询,但现在我不知道如何对它进行分组以获得这样的结果:
Product Name 1
Rep 1 Amount 100pcs
Rep 2 Amount 500pcs
Rep 3 Amount 50pcs
Product name 2
Rep 1 Amount 10pcs
Rep 2 Amount 10pcs
Rep 3 Amount 20pcs
Product name 3
Rep 1 Amount 10pcs
Rep 2 Amount 50pcs
Rep 3 Amount 40pcs
Product name 4
Rep 1 Amount 10pcs
Rep 2 Amount 520pcs
Rep 3 Amount 10pcs
RepsProductsToSubStock (with shema)
Id ProductId RepId
Products (with shema)
ProductId ProductName
Reps (with shema)
RepId RepName RepSurname
$qb = $entityManager->createQueryBuilder();
$result = $qb->select("rptss.Id rep.RepName, rep.RepSurname, prod.ProductName")
->from('RepsProductsToSubStock', 'rptss')
->leftJoin('Reps', 'rep', \Doctrine\ORM\Query\Expr\Join::WITH, 'rep.Id = ppts.PrepId')
->leftJoin('Products', 'prod', \Doctrine\ORM\Query\Expr\Join::WITH, 'prod.ProductId = rptss.ProductId');
$totalRecord = $result->getQuery()->getResult();
因此,这将为我提供所有库存产品,以及正确的产品名称和代表名称。现在我需要分组吗?