A
A
Alexey Arkh2019-05-28 19:07:20
symfony
Alexey Arkh, 2019-05-28 19:07:20

How to create an entity linked in a custom table?

Suggest a solution.
If two entities A and B are interconnected (many-to-many), three tables A, B and A_B are obtained.
I need to display all A, while counting how many B are connected to them.
This is not difficult to do, since there are properties inversedBy and mappedBy, but the problem of the doctrine is that it makes an extra join.
More or less like this:

SELECT A.*, count(DISTINCT Б.ID) FROM A
LEFT JOIN А_Б ON А_Б.A_ID = A.ID
LEFT JOIN Б ON Б.ID =  А_Б.Б_ID

That is, she first looks at the link, then pulls out the connected entities, then counts them, but she could simply count these entities in the link table.
SELECT A.*, count(DISTINCT А_Б.Б_ID) FROM A
LEFT JOIN А_Б ON А_Б.A_ID = A.ID

I got out of the situation by getting $query->getSql(); cutting out an extra LEFT JOIN from there, a request instead of 10 seconds. became fulfilled less than one.
But in another place there is a request that is passed to the paginator, it is additionally filtered and sorted there, cut the request and then return it to such a state that the paginator would accept it, I did not succeed.
It seems like there should be some sort of solution. For example, create a third entity called AB, which will look at table A_B and use it in the query builder.

Many hours of magic
@ORM\MappedSuperclass, Entity(readOnly=true), @ORM\JoinColumn, @ORM\Column, mappedBy, inversedBy

But I can't. Created an entity, excluded it via DoctrineUpdateCommand, so that it would not try to update the table. Registered all connections, but the error is given
The association A#bSimple refers to the owning side field B#bId which does not exist.
Properties bId is the b_id field in the link table,
/**
     * @var integer
     * @ORM\JoinColumn(name="b_id")
     */
    protected $bId;

That is both the field and the table all this is physically.
I thought maybe there is an event in the doctrine that will allow you to correct the SQL before sending it to the database, but I did not find it.

Answer the question

In order to leave comments, you need to log in

Didn't find what you were looking for?

Ask your question

Ask a Question

731 491 924 answers to any question