php - DQL与JOIN Doctrine2和Symfony2

你好，我有两张桌子：产品和存货。他们有一对一的关系，这样看：
股票：

+------------+--------------+------+-----+---------+----------------+
| 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，它给我所有与stock id 1相关联的传递地址，并且只向这个数组添加一个表stock中id 1下的stock地址。数组应该如下所示：

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

最佳答案

您将无法通过一条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'));

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

关于php - DQL与JOIN Doctrine2和Symfony2，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/33211557/