Как создать сущность связанную в кастомной таблицей?

Question

[Алексей Арх]

Подскажите решение.
Если две сущности А и Б, они межу собой связаны (многие ко многим), получается три таблицы A, Б и A_Б.
Мне нужно вывести все А, при этом подсчитав сколько с ними связано Б.
Это сделать не сложно, так как есть свойства inversedBy и mappedBy, но проблема доктрины что она делает лишний join.
Примерно так:

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

То есть она сначала смотрит связку, потом вытаскивает связные сущности, потом считает их, а могла бы просто подсчитать эти сущности в таблице связей.

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

Я вышел из ситуации получая $query->getSql(); вырезая от туда лишний LEFT JOIN, запрос вместо 10 сек. стал выполнятся меньше одной.
Но в другом месте есть запрос, который передаётся в paginator, там дополнительно фильтруется и сортируется, порезать запрос а потом вернуть его в такое состояние что бы paginator его принимал у меня не получилось.

Кажется должно быть какое то адекватное решение. Например создать третью сущность под названием AБ, которая будет смотреть на таблицу A_Б и пользоваться ей в query builder.

Много часов разного волшебства
@ORM\MappedSuperclass, Entity(readOnly=true), @ORM\JoinColumn, @ORM\Column, mappedBy, inversedBy

Но у меня не получается. Создал сущность, исключил её через DoctrineUpdateCommand, что бы не пыталась обновить таблицу. Прописал все связи, но выдаётся ошибка
The association А#bSimple refers to the owning side field Б#bId which does not exist.
Свойства bId это поле b_id в таблице связей,

/**
     * @var integer
     * @ORM\JoinColumn(name="b_id")
     */
    protected $bId;

То есть и поле и таблица всё это есть физически.

Думал может в доктрине есть событие которое позволит поправить SQL перед отправкой в базу, но не нашёл.

Comments

[BoShurik]

А какой пагинатор используете?

[Алексей Арх]

knp paginator

[BoShurik]

Алексей Арх, тогда может вам проще будет использовать hint? https://github.com/KnpLabs/KnpPaginatorBundle/blob...

[Алексей Арх]

BoShurik, спасибо, только завтра смогу посмотреть

[Алексей Арх]

BoShurik, спасибо, это убирает минус 1 запрос) Но не решает проблемы с JOIN.
JOIN добавляет не сам пагинатор, JOIN уже есть в запросе, который передаётся в пагинатор.
Я могу вырезать JOIN из SQL запроса, но такой запрос не принимает пагинатор.

$query = $this->repository->createQueryBuilder('e')
                ->select('e, count(DISTINCT m.id) as materialByTagsCount')
                ->leftJoin('e.materialByTags', 'm')
            ;
            
            $paginate = $this->paginator->paginate(
                $query,
                $this->request->query->get('page', 1),
                25
            );

leftJoin('e.materialByTags', 'm') тут он подключает таблицу связей, по ней подключает материалы, потом считает их.

[BoShurik]

Алексей Арх, что за ошибку выдает пагинатор? Пробовали опцию distinct: false?

[Алексей Арх]

BoShurik, проблема не в пагинаторе.
Если взять $query->getSql(); зареплейсить там лишнее, то потом этот запрос можно выполнить только через обычный PDO, его уже не преобразовать в структуру queryBuilder которая нужна пагинатору что бы дальше продолжить модифицировать sql запрос
Можно на основе запроса попробовать сделать createQueryBuilder($sql), но он будет ругаться что нет сущности АБ.
Хотя может надо создать такую сущность тем самым обманув его.

[BoShurik]

Алексей Арх, пагинатору не обязательно QueryBuilder передавать. Можно Query: https://github.com/KnpLabs/knp-components/blob/mas...

[Алексей Арх]

BoShurik,

$query = $this->entityManager->getConnection()->prepare($querySql);
        $query->execute($queryParameters);

        $paginate = $this->paginator->paginate(
            $query,
            $this->request->query->get('page', 1),
            25,
            [
                'distinct'             => false
            ]
        );

Так ругается пагинатор: One of listeners must count and slice given target

[BoShurik]

Алексей Арх, А если изменять не SQL, а DQL? Тогда потом получится создать объект Query через $this->_em->createQuery($dql)

[Алексей Арх]

BoShurik, Я так пробовал, так докстрина вместо имён таблиц требует имена сущностей и ругается что нету сущности с именем связной таблицы.

Хотя может надо создать такую сущность тем самым обманув его.

[BoShurik]

Алексей Арх, вам проще будет написать кастомный сабскрайбер для пагинатора, чем пытаться костыли в доктрину вставить

[Алексей Арх]

BoShurik,

кастомный сабскрайбер для пагинатора

Можно подробнее?

[BoShurik]

Алексей Арх, https://github.com/KnpLabs/KnpPaginatorBundle/blob...

[Вадим]

Алексей Арх Не пробовали: A - (one to many) - AB - (many to one) - B ? Где AB - отдельная сущность и A с B не имеют прямой связи ManyToMany.

Но это, конечно, жестокий подход.

[Алексей Арх]

Вадим, Если правильно понял, то я пробовал делать AB отдельной сущностью, никак не получилось всё связать что бы работало.

[Вадим]

Алексей Арх Как альтернативное решение могу лишь предложить выбрать сперва сущности A, а затем обычным SQL посчитать сумму из таблицы AB. Должно быстро сработать.

Но как это сделать с использованием doctrine один запросом, пока тоже не подскажу.

[Алексей Арх]

Вадим, проблема в том, что я хочу использовать пагинатор для постранички и сортировки. Если сущности выбрать самому, тогда придётся всё остальное тоже делать вручную. Возможно симфони как то может обработать doctrine событие prePrepare? Если что то подобное есть. Что бы перед отправкой в базу задетектить этот запрос и зареплейстить ненужное.

[Вадим]

Алексей Арх Сортировка по количеству из таблицы B есть?
Если нет, то, как я написал выше, можно через пагинатор выбрать сущности из А, а затем на основе их посчитать сумму из таблицы AB и запихать в объекты.

Если да, то готового решения с доктриной и KnpMenuBundle не подскажу. Можно нарушить консистентность таблиц и добавить в A колонку с количество связей в B, но это тоже так себе подход.

Нужно делать как можно проще, но пока такого решения я не вижу

Answer 1

[Myroslav Berlad]

Попробуйте Doctrine Named Native Query

Comments

[Алексей Арх]

Не получилось. doctrine:schema:validate пишет:

[FAIL] The entity-class TaxonomyBundle\Entity\Taxonomy mapping is invalid:
* The association TaxonomyBundle\Entity\Taxonomy#materialsByTagCount refers to the owning side field MaterialBundle\Entity\MaterialsByTagCount#tagId which is not defined as association, but as field.
* The association TaxonomyBundle\Entity\Taxonomy#materialsByTagCount refers to the owning side field MaterialBundle\Entity\MaterialsByTagCount#tagId which does not exist.

Или такая ошибка

[FAIL] The entity-class TaxonomyBundle\Entity\Taxonomy mapping is invalid:
* The association TaxonomyBundle\Entity\Taxonomy#materialsByTagCount refers to the inverse side field MaterialBundle\Entity\MaterialsByTagCount#id which is not defined as association.
* The association TaxonomyBundle\Entity\Taxonomy#materialsByTagCount refers to the inverse side field MaterialBundle\Entity\MaterialsByTagCount#id which does not exist.
* The referenced column name 'id' has to be a primary key column on the target entity class 'MaterialBundle\Entity\MaterialsByTagCount'.

[Myroslav Berlad]

Алексей Арх, это же не с Named Query проблема, а что-то не так с вашим маппингом.

[Алексей Арх]

Myroslav Berlad, да, что то не складывается