Joining Across a Many-to-Many Relationship

Hey @Cedric

I just gave it a try, and the query works well. I'm confused why adding a select changes the number of fetched rows. Did you download the course code from this page or are you coding it from scratch?

Cheers!

Hi @MolloKhan,

Thanks for your quick reply. I just tried again by mounting the Docker container with the Postgres database, and everything works.
I was using a local MariaDB database during the course.

Ohh that makes sense. I'm glad you found a solution. Cheers!

Hey @Logan-M

We're glad to hear you're liking our content. Can I see your query/code?

Cheers!

Here is my code below. The only difference I notice is the use statement for starshipStatusEnum (Mine is in a different location). I am using a MySQL database not the PostgreSQL.

`<?php

namespace App\Repository;

use App\Entity\Starship;
use App\Model\StarshipStatusEnum;
use Doctrine\Bundle\DoctrineBundle\Repository\ServiceEntityRepository;
use Doctrine\Persistence\ManagerRegistry;
use Pagerfanta\Doctrine\ORM\QueryAdapter;
use Pagerfanta\Pagerfanta;

/**

• @extends ServiceEntityRepository<Starship>
  */
  class StarshipRepository extends ServiceEntityRepository
  {
  public function __construct(ManagerRegistry $registry)
  {

   parent::__construct($registry, Starship::class);
  

  }
  /**

  • @return Starship[]
    */
    public function findIncompleteOrderedByDroidCount(): Pagerfanta
    {
    $query = $this->createQueryBuilder('s')

     ->andWhere('s.status != :status')
     ->orderBy('COUNT(droid)', 'ASC')
     ->leftJoin('s.droids', 'droid')
     ->groupBy('s.id')
     ->setParameter('status', StarshipStatusEnum::COMPLETED)
     ->getQuery()
    

    ;
    return new Pagerfanta(new QueryAdapter($query));
    }
    public function findMyShip(): Starship
    {
    return $this->findAll()[0];
    }
    // /**
    // @return Starship[] Returns an array of Starship objects // /
    // public function findByExampleField($value): array
    // {
    // return $this->createQueryBuilder('s')
    // ->andWhere('s.exampleField = :val')
    // ->setParameter('val', $value)
    // ->orderBy('s.id', 'ASC')
    // ->setMaxResults(10)
    // ->getQuery()
    // ->getResult()
    // ;
    // }
    // public function findOneBySomeField($value): ?Starship
    // {
    // return $this->createQueryBuilder('s')
    // ->andWhere('s.exampleField = :val')
    // ->setParameter('val', $value)
    // ->getQuery()
    // ->getOneOrNullResult()
    // ;
    // }
    }
    `

The raw query looks like this:
SELECT s0_.id AS id_0, s0_.name AS name_1, s0_.captain AS captain_2, s0_.class AS class_3, s0_.status AS status_4, s0_.arrived_at AS arrived_at_5, s0_.slug AS slug_6, s0_.created_at AS created_at_7, s0_.updated_at AS updated_at_8 FROM starship s0_ LEFT JOIN starship_droid s2_ ON s0_.id = s2_.starship_id LEFT JOIN droid d1_ ON d1_.id = s2_.droid_id WHERE s0_.status <> ? GROUP BY s0_.id ORDER BY COUNT(d1_.id) ASC

Thanks for taking the time to look at this.

Hey @Logan-M!

Ah, in recent versions of MySQL, GROUP BY is more strict than Postgres. Basically, you can't do dynamic operations like we're doing in ->orderBy() - it needs to be a column.

Take a look at this comment above for, I think, the solution - this should work in MySQL and Postgres.

I hope this helps!
Kevin

Thanks! That makes sense.

Hey @giorgiocba

I just gave this a try (with the new code) and it works fine. Could you copy-paste the AppFixtures code from this code block and try again?
https://symfonycasts.com/screencast/doctrine-relations/many-to-many-foundry#codeblock-3d67ea2ef7

Cheers!