douqiong8412 2019-06-15 16:50
浏览 182
已采纳

如何使用查询构建器在Symfony 4中的外部ID上连接多个实体?

I'm trying to learn Symfony. Today I was following The associations tutorial. I decided to make a small application that a House, Kitchens, Bedrooms, and cabinets. I (tried to ;-) ) make a small Class diagram using draw.io to give you a better idea.

This is it

So basically a House can have multiple Bedrooms and multiple Kitchens. Each kitchen can have multiple cabinets. The House has an id and a name. The Bedroom and Kitchen as well. The cabinet has id, shopUrl and is also linked via a foreign key (account_id) to its parent Kitchen. I also link the Kitchen and the Bedroom to the House using a foreign key (house_id). So I followed the tutorial and created the House entity:

    <?php

    namespace App\Entity;

    use Doctrine\ORM\Mapping as ORM;
    use Doctrine\Common\Collections\ArrayCollection;
    use Doctrine\Common\Collections\Collection;

    /**
     * @ORM\Entity(repositoryClass="App\Repository\HouseRepository")
     */
    class House implements \JsonSerializable
    {

        /**
         * @ORM\OneToMany(targetEntity="App\Entity\Kitchen", mappedBy="house")
         */
        private $kitchen;

        /**
         * @ORM\OneToMany(targetEntity="App\Entity\Bedroom", mappedBy="house")
         */
        private $bedroom;    

        /**
         * House constructor
         */
        public function __construct()
        {
            $this->kitchen = new ArrayCollection();
            $this->bedroom = new ArrayCollection();
        }

        /**
         * @return Collection|Kitchen[]
         */
        public function getKitchen(): Collection
        {
            return $this->kitchen;
        }

        /**
         * @return Collection|Bedroom[]
         */
        public function getBedroom(): Collection
        {
            return $this->bedroom;
        }


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

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

        public function getId(): ?int
        {
            return $this->id;
        }

        public function getName(): ?string
        {
            return $this->name;
        }

        public function setName(string $name): self
        {
            $this->name = $name;

            return $this;
        }

        public function jsonSerialize()
        {
            return get_object_vars($this);
        }
    }

The House repository is empty (a.k.a: only containts the automatically generated code from Symfony):

    <?php

    namespace App\Repository;

    use App\Entity\House;
    use Doctrine\Bundle\DoctrineBundle\Repository\ServiceEntityRepository;
    use Symfony\Bridge\Doctrine\RegistryInterface;

    /**
     * @method House|null find($id, $lockMode = null, $lockVersion = null)
     * @method House|null findOneBy(array $criteria, array $orderBy = null)
     * @method House[]    findAll()
     * @method House[]    findBy(array $criteria, array $orderBy = null, $limit = null, $offset = null)
     */
    class HouseRepository extends ServiceEntityRepository
    {
        public function __construct(RegistryInterface $registry)
        {
            parent::__construct($registry, House::class);
        }

    }

The Bedroom entity is this:

    <?php

    namespace App\Entity;

    use Doctrine\ORM\Mapping as ORM;

    /**
     * @ORM\Entity(repositoryClass="App\Repository\BedroomRepository")
     */
    class Bedroom implements \JsonSerializable
    {

        /**
         * @ORM\ManyToOne(targetEntity="App\Entity\House", inversedBy="bedroom")
         */
        private $house;

        public function getHouse(): House
        {
            return $this->house;
        }

        public function setHouse(House $house): self
        {
            $this->house = $house;

            return $this;
        }


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

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

        public function getId(): ?int
        {
            return $this->id;
        }

        public function getName(): ?string
        {
            return $this->name;
        }

        public function setName(string $name): self
        {
            $this->name = $name;

            return $this;
        }

        public function jsonSerialize()
        {
            return get_object_vars($this);
        }
    }

and the Bedroom repository is also empty.

The Kitchen entity has the following code:

    <?php

    namespace App\Entity;

    use Doctrine\ORM\Mapping as ORM;

    /**
     * @ORM\Entity(repositoryClass="App\Repository\KitchenRepository")
     */
    class Kitchen implements \JsonSerializable
    {


        /**
         * @ORM\OneToMany(targetEntity="App\Entity\Cabinet", mappedBy="kitchen")
         */
        private $cabinet;

        /**
         * Kitchen constructor
         */
         public function __construct()
        {
            $this->cabinet= new ArrayCollection();
        }

        /**
         * @return Collection|Cabinet[]
         */
        public function getCabinet(): Collection
        {
            return $this->cabinet;
        }


        /**
         * @ORM\ManyToOne(targetEntity="App\Entity\House", inversedBy="kitchen")
         */
        private $house;

        public function getHouse(): House
        {
            return $this->house;
        }

        public function setHouse(House $house): self
        {
            $this->house = $house;

            return $this;
        }

        /**
         * @ORM\Id()
         * @ORM\GeneratedValue(strategy="UUID")
         * @ORM\Column(type="integer")
         */
        private $id;

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

        public function getId(): int
        {
            return $this->id;
        }

        public function getName(): string
        {
            return $this->name;
        }

        public function setName(string $name): self
        {
            $this->name = $name;

            return $this;
        }

        public function jsonSerialize()
        {
            return get_object_vars($this);
        }
    }

and the Kitchen repository is also empty. Finally, the cabinet consists of the following:

    <?php

    namespace App\Entity;

    use DateTime;
    use Doctrine\ORM\Mapping as ORM;

    /**
     * @ORM\Entity(repositoryClass="App\Repository\CabinetRepository")
     */
    class Cabinet
    {
        /**
         * @ORM\Id()
         * @ORM\GeneratedValue()
         * @ORM\Column(type="integer")
         */
        private $id;

        /**
         * @ORM\Column(type="string", length=255)
         */
        private $shopUrl;

        private $account_id;

        /**
         * @ORM\ManyToOne(targetEntity="Kitchen", inversedBy="cabinet")
         */
        private $kitchen;

        /**
         * Cabinet constructor.
         * @param string $shopUrl
         * @param Kitchen $kitchen
         * @param int $id
         */
        public function __construct(string $shopUrl, Kitchen $kitchen = null, int $id = null)
        {
            $this->shopUrl = $shopUrl;
            $this->kitchen = $kitchen;
            $this->id = $id;
        }

        public function setId(int $id): self
        {
            $this->id = $id;

            return $this;
        }

        public function getId(): int
        {
            return $this->id;
        }


        public function getShopUrl(): string
        {
            return $this->shopUrl;
        }

        public function getKitchen(): Kitchen
        {
            return $this->kitchen;
        }

        public function setKitchen(Kitchen $kitchen): self
        {
            $this->kitchen = $kitchen;
            $this->account_id = $kitchen->getId();
            return $this;
        }

        public function setAccount_id(int $account_id): self
        {
            $this->account_id = $account_id;

            return $this;
        }

        public function getAccount_id(): int
        {
            return $this->account_id;
        }

    }

In contrast to the other entities, the cabinet has some logic (this is where I actually need help). Since Bedroom and Kitchen are associated with a House, I would like to give a Bedroom, then look up all the kitchens associated with the same house as the Bedroom and then return all cabinets that these kitchens have. I know it may seem illogical but I discovered this too late to come up with another concept. My current code doesn't work because I'm not sure whether this is possible and because it's a bit too complex for me to grasp at this moment. But I have this as the content of the cabinet repo:


    <?php

    namespace App\Repository;

    use App\Entity\Bedroom;
    use App\Entity\House;
    use App\Entity\Cabinet;
    use App\Entity\Kitchen;
    use Doctrine\Bundle\DoctrineBundle\Repository\ServiceEntityRepository;
    use Symfony\Bridge\Doctrine\RegistryInterface;

    /**
     * @method Cabinet|null find($id, $lockMode = null, $lockVersion = null)
     * @method Cabinet|null findOneBy(array $criteria, array $orderBy = null)
     * @method Cabinet[]    findAll()
     * @method Cabinet[]    findBy(array $criteria, array $orderBy = null, $limit = null, $offset = null)
     */
    class CabinetRepository extends ServiceEntityRepository
    {
        public function __construct(RegistryInterface $registry)
        {
            parent::__construct($registry, Cabinet::class);
        }

        public function findByBedroom(Bedroom $bedroom) //use joins??
        {
            return $this->createQueryBuilder('cabinet')
                ->join('cabinet.bedroom', 'bedroom')
                ->join('cabinet.kitchen', 'kitchen')
                ->addSelect('cabinet')
                ->andWhere('cabinet.bedroom = :idBedroom')
                ->setParameter('idBedroom', $bedroom->getId())
                ->orderBy('time', 'ASC')
                ->getQuery()
                ->getResult();
        }
    }

I'm using PHPStorm and no error are showing anywhere but of course, the querybuilder doesn't return anything. How can I fix it? I couldn't find any questions that try to achieve what I'm trying to do.

I added data manually to the database, so there's data in there. And using Sequel Pro I can see the relations. The data (as far as I'm concerned) is fine. The queryBuilder is where the mistakes are.

A demonstrative example with some data:

This is the Bedroom data:

id     name              house_id  
325    bigBedroomOne     1666   
815    smallBedroomOne   555   
902    bigBedroomTwo     1666

This is the House data:

id     name          
1666   bigHouse      
555    smallHouse      

This is the Kitchen data:

id   name              house_id
1    bigKitchen        1666
2    smallKitchen      555
55   mediumKitchen     555

And finally, this is the cabinets data:

id  shopUrl    account_id
1   ur.l       55
88  co.m       2
33  ne.t       1

So in this example I would like to plug in the Bedroom id 815 which is associated with the house_id 555. Then from there all the Kitchen associated with that house_id, should be selected, so 2 and 55. Finally, the cabinets with id 1 and 88 should be returned.

Edit: When running bin/console doc:sch:val I get this back:

`Mapping

[OK] The mapping files are correct.

Database

[OK] The database schema is in sync with the mapping files.`

  • 写回答

3条回答 默认 最新

  • doulan8330 2019-06-17 22:11
    关注

    There are several minor problems in your code, more on that later.

    Here is \App\Repository\CabinetRepository::findByBedroom:

        public function findByBedroom(Bedroom $bedroom) //use joins??
        {
            return $this->createQueryBuilder('cabinet')
                ->join('cabinet.kitchen', 'kitchen')
                ->join('kitchen.house', 'house')
                ->join('house.bedroom', 'bedroom')
                ->addSelect('cabinet')
                ->andWhere('bedroom = :bedroom')
                ->setParameter('bedroom', $bedroom)
                ->getQuery()
                ->getResult();
        }
    

    For bedroom entity with ID 815 the code above returns the following (formatted as symfony/var-dumper would do that):

    array:2 [▼
      0 => Cabinet {#387 ▼
        -id: 88
        -shopUrl: "co.m"
        -account_id: null
        -kitchen: Kitchen {#354 ▼
          +__isInitialized__: false
          -cabinet: null
          -house: null
          -id: 2
          -name: null
           …2
        }
      }
      1 => Cabinet {#364 ▼
        -id: 1
        -shopUrl: "ur.l "
        -account_id: null
        -kitchen: Kitchen {#370 ▼
          +__isInitialized__: false
          -cabinet: null
          -house: null
          -id: 55
          -name: null
           …2
        }
      }
    ]
    

    Note: house references are null because of lazy loading.

    So, small problems in your code:

    1. Your query in CabinerRepository was doing wrong joins. For correct joins see code above.

    2. That query referring to unknown field time. I have removed that reference.

    3. And also was using bedroom ID instead of bedroom entity.

    4. Your Kitchen.php is incomplete, it refers Collection and ArrayCollection classes, but there are no corresponding use directives. Just add this after namespace before class:

    use Doctrine\Common\Collections\ArrayCollection;
    use Doctrine\Common\Collections\Collection;
    

    Update: here is how to get repository reference:

    public function myControllerAction(/* other parameters */, CabinetRepository $cabRepo)
    {
        $cabinet = $cabRepo->find($id);
        // OR, if you don't want to add parameter for some reason:
        $cabRepo = $this->getDoctrine()->getRepository(Cabinet::class);
        $cabinet = $cabRepo->find($id);
    }
    
    本回答被题主选为最佳回答 , 对您是否有帮助呢?
    评论
查看更多回答(2条)

报告相同问题?

悬赏问题

  • ¥15 wamp3.3.5安装完成后图标正常显示绿色,鼠标左右键点击图标均无反应。求解决方法。
  • ¥15 鼠标点击的这条记录了什么?
  • ¥15 在写pid调速的程序时,电机始终维持最大速度
  • ¥15 请问如何查看手机root记录?
  • ¥15 商城小程序订单号重复
  • ¥15 学校优化算法sbo和蚁群算法怎么结合
  • ¥21 matlab怎么求时域信号的二阶导数
  • ¥15 判断两个表是否完全相同
  • ¥15 java map类型数据格式,如何快速通过前缀匹配元素
  • ¥15 stc12c5a60s2、QMC5883L、LCD1602组合测量磁场所需程序