Symfony : How to sort ArrayCollection in a OneToMany relation (3 entities)?

Question

I have a product which can has one or many tags.

For a particular reason, I have 3 entities which correspond to :

• Product
• Tag
• ProductTag (relation) :

I want to order by type my ArrayCollection of tags. This collection contains ProductTag entities.

The difficulty is that I haven't a type property in ProductType, this property is in Tag entity.

Product

/**
 * @ORM\OneToMany(targetEntity="ProductTag", mappedBy="product", cascade={"persist", "remove"})
 * @Assert\Valid
 */
private $tags;

Tag

/**
 * @ORM\Column(type="string", length=15)
 */
private $type;

ProductTag

/**
 * @ORM\Column(name="Product_id", type="integer")
 * @ORM\Id
 */
private $product_id;

/**
 * @ORM\Column(name="Tag_id", type="integer")
 * @ORM\Id
 */
private $tag_id;

/**
 * @ORM\ManyToOne(targetEntity="Product", inversedBy="tags")
 * @ORM\JoinColumn(name="Product_id", referencedColumnName="Product_id")
 */
private $product;

/**
 * @ORM\ManyToOne(targetEntity="Tag")
 * @ORM\JoinColumn(name="Tag_id", referencedColumnName="Tag_id")
 */
private $tag;

The solution below can't work because ProductTag hasn't a type property :

// Product entity

/**
 * @ORM\OneToMany(targetEntity="ProductTag", mappedBy="product", cascade={"persist", "remove"})
 * @ORM\OrderBy({"type" = "ASC"})
 * @Assert\Valid
 */
private $tags;

I would like to do something like this @ORM\OrderBy({"tag.type" = "ASC"}).

Any idea ?

Thanks.

--- EDIT ---

In PhpMyAdmin, I created a view which gets the data of tags and the product id for each tags.

Then, in Symfony, I created an entity that corresponds to my view. In the Repository of this entity, I created a query that concats my tags by their type.

public function findByProductGroupByType($productId)
{
   return $this->getEntityManager()
        ->createQuery(
            'SELECT v.tagTypeCode, v.tagType,
                GROUP_CONCAT(v.tagCode) as tagCode,
                GROUP_CONCAT(v.tagName) as tagName,
                GROUP_CONCAT(v.picto) as tagPicto
                FROM AppBundle:ProductTagView v
                WHERE v.productId = :id
                GROUP BY v.tagTypeCode
                ORDER BY v.tagType ASC'
        )->setParameter('id', $productId)
        ->getResult();
}

So that it works, it is necessary to install this bundle so that Symfony recognizes GROUP_CONCAT. After installation, add this in config.yml :

doctrine:
    orm:
        dql:
            string_functions:
                group_concat: DoctrineExtensions\Query\Mysql\GroupConcat

The complete list is here : https://github.com/beberlei/DoctrineExtensions/blob/master/config/mysql.yml

The query returns something like this :

array (size=2)
  0 => 
    array (size=5)
      'tagTypeCode' => string 'KFEAT' (length=5)
      'tagType' => string 'Key Feature' (length=11)
      'tagCode' => string 'double_sanglage' (length=15)
      'tagName' => string 'double sanglage' (length=15)
      'tagPicto' => string 'double_sanglage.jpg' (length=19)
  1 => 
    array (size=5)
      'tagTypeCode' => string 'SIZE' (length=4)
      'tagType' => string 'Size' (length=4)
      'tagCode' => string 'h26_ceintures,h21_ceintures' (length=27)
      'tagName  => string 'ceintures h26cm,ceintures H21 cm' (length=32)
      'tagPicto' => string 'h26_ceintures.jpg,h21_ceintures.jpg' (length=35)

The tags of type SIZE are concatenated with , separator.

Answer 1

You can't do it directly. But there is a workaround.

• Add a ordered tags property to you Product entity :

  private $orderedTags;
  
  public function __construct()
  {
      //...
      $this->orderedTags = new \Doctrine\Common\Collections\ArrayCollection();
  }
  

• Create a new doctrine Event Listener and register in in services.yml

  services:
      my.listener:
          class: AppBundle\EventListener\OrderedTagsListener
          arguments: ["@doctrine.orm.entity_manager"]
          tags:
              - { name: doctrine.event_listener, event: postLoad }
  
  
  
  // src/AppBundle/EventListener/OrderedTagsListener.php
  namespace AppBundle\EventListener;
  
  use Doctrine\ORM\Event\LifecycleEventArgs;
  use AppBundle\Entity\Product;
  
  class OrderedTagsListener
  {
      private $em;
  
      public function __construct($em)
      {
          $this->em = $em;
      }
  
      public function postPersist(LifecycleEventArgs $args)
      {
          $product = $args->getEntity();
  
          // Retrieve your tags ordered with a query defined in your tags repository
          $orderedTags = $this->em->getManager()->getRepository('AppBundle:Tags')->getOrderedTags($product);
  
          $product->setOrderedTags();
      }
  }