3

I need to "connet" an instance of HolidayPackage to Discount (one-to-one) and be able to fetch join when getting data from the repository. I.e:

 // Inside a method of HolidayPackageRepository repository class $qb = $this->createQueryBuilder($alias = 'hp'); $qb->leftJoin("$alias.discount", 'd') ->addSelect('d'); // fetch join

The problem is that discount table is deleted and filled again every couple of hour, so discount_id cannot be a real foreign key. However identifiers may stay the same:

/** * @ORM\Entity() * @ORM\Table("holiday_package") */ class HolidayPackage { /** * @ORM\Column(name="discount_id", type="integer", nullable=true) * * @var null|Discount */ private $discount; }

How can I fetch join without a real foreign key?

Improve this question
2
  • You will probably just have to make a second query or drop down to sql. Commented Jul 12, 2014 at 14:50
  • @Cerad This is what I'm currently doing, I was looking for a better way... Commented Jul 12, 2014 at 17:09

2 Answers 2

Reset to default
1

I have never actually tried it, but according to the docs, starting from 2.4, it is possible to join entities on arbitrary columns, as long as both entities are mapped, with the "usual" JOIN ... WITH syntax:

SELECT u FROM User u JOIN Blacklist b WITH u.email = b.email

However, I'm not sure if it will allow you to actually fetch-join "Blacklist" in the example above, or it will just allow you to limit the results.

Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

0

You can specify relation on your entities even if you do not have foreign keys on your db

/** * @ORM\Entity() * @ORM\Table("holiday_package") */ class HolidayPackage { /** * @ORM\Column(name="discount_id", type="integer", nullable=true) * * @var null|int */ private $discountId; /** * @ORM\OneToOne(targetEntity="Discount") * @ORM\JoinColumn(name="discount_id", referencedColumnName="discount_id") * * @var null|Discount */ private $discount; }

This way your join should work.

Improve this answer

5 Comments

@gremo I do not agree. It specifies a relation not a foreign key. It allows joins to be made which would also be possible in SQL even if the is no foreign key defined.
You are defining a "standard" column discount_id and then the one-to-one relation that would create the same column discount_id. If I'm right, the one-to-one relation owns the target entity and thus it holds the foreign key. It may work (i did not tested it) but what if you switch the order of annotations? Will it work too? Btw, I was thinking at another approach: use standard foreign keys but disabling them during the update/sync process, preventing the cascade deletion of children. What do you think?
I tried your method and it doesn't work. Symfony profiler gives a mapping error: the association Discount#holiday_package refers to the owning side field HolidayPackage#discount which is not defined as association, but as field.
@gremo the error seems to be on your discount class. Is the id on Discount really called discount_id or just id? What does your Discount class look like? You might want to check docs.doctrine-project.org/en/2.0.x/reference/… for the correct annotations.
unfortunately it doesn't work - it tries to create a foreign key anyway

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.