Is it possible in Doctrine 2 to link several tables to one through an intermediate table?

Question

PHP

I've been trying to write annotations for entities for 2 days, and I can't figure out how to do it, if at all possible.
I have the following tables in Postgresql:

Language -> id, iso_code
I18n_Entity -> id
I18n_String -> id, i18n_entity_id, language_id, text
Product -> id, i18n_entity_id(unique)
Category -> id, i18n_entity_id(unique)

We need to create 2 functions
getProductByName(Language $language, $name);
If I used pure SQL then the query would be like this

SELECT p.*, i18s.text
FROM product as p
INNER JOIN I18n_String AS i18s ON i18s.i18n_entity_id = p.i18n_entity_id AND i18s.language_id = :language_id
WHERE i18s.text = :name

getCategoryByName(Language $language, $name);

SELECT c.*, i18s.text
FROM category as c
INNER JOIN I18n_String AS i18s ON i18s.i18n_entity_id = c.i18n_entity_id AND i18s.language_id = :language_id
WHERE i18s.text = :name

The request goes like this: product -> i18n_entity -> i18n_string <- language .
Or in short product -> i18n_string <- language .
Internationalization is used on the site, and the whole point is that the words are stored in a separate table, which is associated with several tables, not only product and category.
Is it actually possible to describe such relationships using Doctrine 2, and if so, how?

Answer 1

Well, as I understand it, in order to use DQL , the relationship of the tables must be defined in the annotations.
If you use a DQL join between Product and I18nString through I18nEntity, which is mapped onto the i18n_entity table, then how to register a relationship on the i18n_entity.id primary key at least in one direction on the i18n_string.entity_id field, and if you do not use i18nEntity, then how can you do from one field i18n_string.entity_id two or more foreign keys to different tables, you can’t do it in the database, and in principle this is somehow not true?
i.e. it should be right

SELECT p.*, i18s.text
FROM product as p
INNER JOIN i18n_entity AS i18e ON i18e.id = p.i18n_entity_id
INNER JOIN i18n_String AS i18s ON i18s.i18n_entity_id = i18e.id AND i18s.language_id = :language_id
WHERE i18s.text = :name

In DQL it would be something like

select p, i18s
FROM \Application\Entity\Product p
JOIN p.i18nEntity i18n
JOIN i18n.id i18s
JOIN i18s.language l
WHERE l.id = :language AND i18s.text = :name

but then I'm missing the JOIN i18n.id i18s relationship