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条回答 默认 最新

相关推荐 更多相似问题