CODEWITHSUNDEEP
phpsymfonydoctrine-ormorm
iBadGamer
iBadGamer

Reputation: 606

Doctrine two fields for the same entity, get related for both fields

I have 2 entities, Reservation which is a reservation for a ride like Uber. The reservation have a field pickUpAdress and a field dropOffAdress:

Reservation:

/**
 * @Assert\Valid()
 * @ORM\ManyToOne(targetEntity="Address", cascade={"all"}, fetch="EAGER")
 * @ORM\JoinColumn(name="pick_up_address_id", referencedColumnName="id")
 */
protected $pickUpAddress;

/**
 * @Assert\Valid()
 * @ORM\ManyToOne(targetEntity="Address", cascade={"all"}, fetch="EAGER")
 * @ORM\JoinColumn(name="drop_off_address_id", referencedColumnName="id")
 */
protected $dropOffAddress;

And I added these annotations in my Adress class:

/**
 * @var ArrayCollection
 * @ORM\OneToMany(targetEntity="Reservation",
 *     mappedBy="pickUpAddress",
 *     cascade={"all"})
 */
private $pickUpReservations;

/**
 * @var ArrayCollection
 * @ORM\OneToMany(targetEntity="Reservation",
 *     mappedBy="dropOffAddress",
 *     cascade={"all"})
 */
private $dropOffReservations;

And my form:

->add(
        'pickUpAddress',
        EntityType::class,
        array(
            'class'              => Address::class,
            'property'           => 'display',
            'label'              => 'pick_up_address',
            'translation_domain' => 'front',
            'empty_value'        => 'new_address',
            'query_builder'      => function (AddressRepository $addressRepository) {
                return $addressRepository->getAdressesByUser($this->user);
            },
            'required'           => false
        )

I want in a form for a new Reservation to display the list of all adresses previsously selected for the given user.

E.g: If I make a reservation with my home adress for the pick up one and my office adress for the drop off one. For my next reservation, I want two select (html select) with my home adress and my office adress for both fields dropOffAdress and pickUpAdress.

I have the SQL for this:

SELECT * FROM `address` 
JOIN reservation 
    on reservation.pick_up_address_id = address.id 
        OR reservation.drop_off_address_id = address.id

I just need to add the where clause to select only the one of the logged in user but that is not the issue here. I cannot manage to do the join with a query builder. I have tried many combinations like:

$qb->select('pick_up_address', 'drop_off_adress')
    ->from(Reservation::class, 'reservations')
    ->join('reservations.pickUpAddress', 'pick_up_address')
    ->join('reservations.dropOffAddress', 'drop_off_adress')

$qb->select('Address')
        ->from(Address::class, 'Address')
        ->join('Address.pickUpReservations', 'pick_up_reservations')
        ->join('Address.dropOffReservations', 'drop_off_reservations')

But it does not work. If I use DQL it does work (my repository give me the results) but the form builder want a queryBuilder object and not the array of Adress

Upvotes: 1

Views: 1341

Answers (1)

iBadGamer
iBadGamer

Reputation: 606

I cleared my cache, added the inversed by annotation:

 /**
 * @Assert\Valid()
 * @ORM\ManyToOne(targetEntity="Address", cascade={"all"}, fetch="EAGER", inversedBy="pickUpReservations")
 * @ORM\JoinColumn(name="pick_up_address_id", referencedColumnName="id")
 */
protected $pickUpAddress;

/**
 * @Assert\Valid()
 * @ORM\ManyToOne(targetEntity="Address", cascade={"all"}, fetch="EAGER", inversedBy="dropOffReservations")
 * @ORM\JoinColumn(name="drop_off_address_id", referencedColumnName="id")
 */
protected $dropOffAddress;

And transform the last one to use left join:

 return $qb->select('Address')
        ->leftjoin('Address.pickUpReservations', 'pickUpReservations')
        ->leftjoin('Address.dropOffReservations', 'dropOffReservations');

And it seems to work

Upvotes: 1

Related Questions