php - DQL 与 JOIN Doctrine2 和 Symfony2

Question

您好，我有两个表:产品和库存。他们有 manyToOne 关系，看起来是这样的:

库存:

+------------+--------------+------+-----+---------+----------------+
| Field      | Type         | Null | Key | Default | Extra          |
+------------+--------------+------+-----+---------+----------------+ 
| id         | int(11)      | NO   | PRI | NULL    | auto_increment |
| stock_name | varchar(255) | NO   |     | NULL    |                | 
| address    | varchar(255) | NO   |     | NULL    |                |
+------------+--------------+------+-----+---------+----------------+

产品:

+----------+--------------+------+-----+---------+----------------+
| Field    | Type         | Null | Key | Default | Extra          |
+----------+--------------+------+-----+---------+----------------+
| id       | int(11)      | NO   | PRI | NULL    | auto_increment |
| stock_id | int(11)      | YES  | MUL | NULL    |                |
| name     | varchar(255) | NO   |     | NULL    |                |
| delivery | varchar(255) | NO   |     | NULL    |                |
+----------+--------------+------+-----+---------+----------------+

实体

class Products
{
    /**
     * @ORM\Id
     * @ORM\GeneratedValue
     * @ORM\Column(type="integer")
     */
    private $id;

    /**
     * @ORM\Column(type="string")
     */
    private $name;

    /**
     * @ORM\Column(type="string")
     */
    private $delivery;

    /**
     * @ORM\ManyToOne(targetEntity="Stock", inversedBy="products")
     * @ORM\JoinColumn(nullable=true)
     */
    private $stock;

    /**
     * @return mixed
     */
    public function getName()
    {
        return $this->name;
    }

    /**
     * @param mixed $name
     */
    public function setName($name)
    {
        $this->name = $name;
    }

    /**
     * @return mixed
     */
    public function getDelivery()
    {
        return $this->delivery;
    }

    /**
     * @param mixed $delivery
     */
    public function setDelivery($delivery)
    {
        $this->delivery = $delivery;
    }

    /**
     * @return mixed
     */
    public function getStock()
    {
        return $this->stock;
    }

    /**
     * @param mixed $stock
     */
    public function setStock(Stock $stock)
    {
        $this->stock = $stock;
    }

    /**
     * @return mixed
     */
    public function getId()
    {
        return $this->id;
    }
}

class Stock
{
    /**
     * @ORM\Id
     * @ORM\GeneratedValue
     * @ORM\Column(type="integer")
     */
    private $id;

    /**
     * @ORM\Column(type="string")
     */
    private $stock_name;

    /**
     * @ORM\Column(type="string")
     */
    private $address;

    /**
     * @ORM\OneToMany(
     *      targetEntity="Products",
     *      mappedBy="stock",
     *      orphanRemoval=true
     * )
     */
    private $products;

    /**
     * @return mixed
     */
    public function getStockName()
    {
        return $this->stock_name;
    }

    /**
     * @param mixed $stock_name
     */
    public function setStockName($stock_name)
    {
        $this->stock_name = $stock_name;
    }

    /**
     * @return mixed
     */
    public function getAddress()
    {
        return $this->address;
    }

    /**
     * @param mixed $address
     */
    public function setAddress($address)
    {
        $this->address = $address;
    }

    /**
     * @return mixed
     */
    public function getId()
    {
        return $this->id;
    }

    /**
     * @return mixed
     */
    public function getProducts()
    {
        return $this->products;
    }

    /**
     * @param mixed $products
     */
    public function setProducts($products)
    {
        $this->products = $products;
    }
}

我需要 sql 来为我提供与股票 ID 1 关联的所有送货地址，并仅将表 Stock 中 ID 1 下的一个股票地址添加到此数组。该数组应如下所示:

Array ( 
    [0] => Stock Address
    [1] => delivery1
    [2] => delivery2
    [3] => delivery3
)

我尝试了这条语句，但它给出了多个股票地址和数组中的数组:

$qb = $this->getEntityManager()->createQuery(
    "SELECT o.delivery , p.address
        FROM AppBundle:Products o 
        JOIN o.stock p
        WHERE o.stock =1
    ");

return $qb->getScalarResult();

以及这条语句的结果:

Array ( 
    [0] => Array ( 
        [delivery] => Spoon str, USA 
        [address] => Wall street 1, USA 
    ) 
    [1] => Array ( 
        [delivery] => Lincoln street, USA 
        [address] => Wall street 1, USA 
    )
)

Answer 1

您将无法通过一个 DQL 语句直接检索您想要的数组。原因是您实际上希望示例中提取的四行来自两个不同的列。

因此您需要以某种方式处理结果。例如，您可以将代码段更改为:

$qb = $this->getEntityManager()->createQuery(
"SELECT o.delivery , p.address
    FROM AppBundle:Products o 
    JOIN o.stock p
    WHERE o.stock = 1
");

$result = $qb->getScalarResult();

return array_merge([$result[0]['address']], array_column($result, 'delivery'));

因此您将地址作为要返回的数组的第一个元素，并仅添加所有结果的交付列并添加它们。