Fetch distinct rows without doubled property values on MySql DB with Symfony 3.4 and Doctrine

Question

In a Symfony 3.4 Application I have an Entity with 4 properties plus the id.

It is managed by doctrine on a mySql DB.

Say the properties where named p1, p2, p3 and q. An example DB table could be like this:

id  p1  p2  p3  q
------------------
1   x   y   z   1
2   x   y   n   2
3   x       z   1
4   x       z   2
5   x   y   z   3
6   x   y   z   4
7   x   n   z   1

What I need to do is to request all entities from the DB that have different combinations of p1, p2, p3. So using the given sample DB table the result I need would be

id  p1  p2  p3  q
------------------
1   x   y   z   1
2   x   y   n   2
3   x       z   1
7   x   n   z   1

So the rows with id's 4, 5 and 6 wouldn't be in the set because the have "doubled" combinations of p1, p2, p3.

Now - how can I do this using the methods on the Repository class of Symfony 3.4 like described here:

https://symfony.com/doc/3.4/doctrine.html

Or are there any other ways to achieve what I am looking for?

Any hints are very welcome.

EDIT: Basically I am looking for a list of all existing combinations of p1, p2, p3 without having a combination doubled in the list. It doesn't matter which row is returned in the result set (regarding the properties id and q) as long as one (and only one) row of each combination of p1, p2, p3 is included.

Answer 1

A "group by" subquery to eliminate duplicated data, and a main query to search from ids:

$dql = 'SELECT r FROM myTable r
WHERE r.id IN (
    SELECT min(s.id) FROM myTable s
    GROUP BY s.p1, s.p2, s.p3
)';

$rows = $em->createQuery($dql)
    ->getResult();

Answer 2

If you only want the differents uniques combination of p1, p2 and p3 and don't care about id and q, you can exclude them from your query and use a distinct clause.

There is no built-in method of the Repository class for your need, you might want to create a custom repository class (I personnally do it every time I have to write custom queries for my entities)

Let's consider your entity (located in example in src/AppBundle/Entity/MyEntity.php) declared such way :

namespace AppBundle\Entity;

use Doctrine\ORM\Mapping as ORM;

/**
 * @ORM\Entity(repositoryClass="AppBundle\Repository\MyEntityRepository")
 */
class MyEntity
{
    //declarations of $id, $p1 and so on...
}

You can create a custom repository class (in example src/AppBundle/Repository/MyEntityRepository.php) with a method to get the needed result :

namespace AppBundle\Repository;

use Doctrine\ORM\EntityRepository;

class MyEntityRepository extends EntityRepository
{
    public function findAllUniqueCombinations()
    {
        $result = $this->createQueryBuilder('m')
                       ->select('m.p1 p1', 'm.p2 p2', 'm.p3 p3')
                       ->distinct()
                       ->getQuery()
                       ->getResult();

        return ($result);
    }
}

And, in some controller action :

$MyCombinationList = $this->getDoctrine()
                          ->getManager()
                          ->getRepository(MyEntity::class)
                          ->findAllUniqueCombinations();

---

I used quite the same query for testing (only the select() changed)

Input datas

id | name       | firstname
---+------------+---------- 
 1 | Smith      | John 
 2 | Smith      | John 
 3 | Smith      | John 
 4 | Smith      | John 
 5 | Doe        | Jane 
 6 | Connor     | Sarah 
 7 | The Sponge | Bob 
 8 | Marley     | Bob

The select was ->select('c.name name', 'c.firstname firstname')

The output var_dump($result); gave :

array (size=5)
  0 => 
    array (size=2)
      'name' => string 'Smith' (length=5)
      'firstname' => string 'John' (length=4)
  1 => 
    array (size=2)
      'name' => string 'Doe' (length=3)
      'firstname' => string 'Jane' (length=4)
  2 => 
    array (size=2)
      'name' => string 'Connor' (length=6)
      'firstname' => string 'Sarah' (length=5)
  3 => 
    array (size=2)
      'name' => string 'The Sponge' (length=9)
      'firstname' => string 'Bob' (length=3)
  4 => 
    array (size=2)
      'name' => string 'Marley' (length=6)
      'firstname' => string 'Bob' (length=3)