php - 查询并返回不同值和数量总和

Question

我在 Symfony 2 中有一个查询，我试图从带有连接的表中仅返回唯一值以及这些唯一值的总数量。

例如:

Inventory Table: 

id | product_id | qty  | warehouse_id
----------------------------------------
1  | 123        | 5    | 1
2  | 123        | 5    | 1
3  | 123        | 5    | 1
4  | 234        | 5    | 1 
5  | 345        | 5    | 1
6  | 345        | 5    | 1
7  | 345        | 5    | 1  
8  | 345        | 5    | 1  
9  | 345        | 5    | 1  
10 | 345        | 5    | 1  

Product Table: 

id   | name       | sku        | description    |
------------------------------------------------
123  | Test 123   | Test 123   | Test 123
234  | Test 234   | Test 234   | Test 234
345  | Test 345   | Test 345   | Test 345

所以最后，我在数组中需要的几乎是以下信息以及从连接返回的信息:

product_id: 123   qty: 15
product_id: 234   qty: 5
product_id: 345   qty: 30

这是我的完整查询...我只是不太确定如何在 Symfony 2 中按照该原则执行此操作。感谢您的帮助!

$search_string = trim($search_string);
$em = $this->getEntityManager();
$query = $em->createQuery("
    SELECT    i, p, il, w
    FROM      WIC\InventoryBundle\Entity\Inventory i
    LEFT JOIN i.product p
    LEFT JOIN i.inventoryLocation il
    LEFT JOIN il.warehouse w
    WHERE     (p.name LIKE :tag OR p.sku LIKE :tag OR p.description LIKE :tag)
    AND       p.account = :account_id
    AND       il.warehouse = :location_id");
$query->setParameters(array(
    'tag'           => "%$search_string%",
    'account_id'    => $account_id,
    'location_id'    => $location_id
));
return $query->getArrayResult();

Answer 1

你可以试试

$search_string = trim($search_string);
$q = $this->getEntityManager()
          ->getRepository('YourBundle:Inventory')
          ->createQueryBuilder('i');
$query = $q->select('SUM(DISTINCT(i.qty))')
    ->leftJoin('i.product', 'p')
    ->leftJoin('i.inventoryLocation', 'il')
    ->where($q->exp()->like('p.name', ':tag')
    ->orWhere($q->exp()->like('p.sku', ':tag')
    ->orWhere($q->exp()->like('p.description', ':tag')
    ->andWhere('p.account = :account_id')
    ->andWhere('il.warehouse = :location_id')
    ->setParameter('tag', $q->expr()->literal("%$search_string%"))
    ->setParameter('account_id', $account_id)
    ->setParameter('location_id', $location_id)
    ->getQuery();

return $query->getResult();

我不知道你的实体字段名到底是什么，所以根据你的实体映射中的实际内容来纠正它们