Doctrine orderBy annotation. Ordering Entity associations based on an associated Entity.

Question

Say have the following entities with a Symfony app.

class List
{
    /**
     * @ORM\OneToMany(targetEntity="ListItem", mappedBy="list")
     * @ORM\OrderBy({"category.title" = "ASC"})
     */
    protected $listItems;
}

class ListItem
{
    /**
     * @ORM\ManyToOne(targetEntity="List", inversedBy="listItems")
     */
    protected $list;

    /**
     * @ORM\ManyToOne(targetEntity="Category", inversedBy="listItems")
     */
    protected $category;
}

class Category
{
    /**
     * @ORM\OneToMany(targetEntity="ListItem", mappedBy="cateogory")
     */
    protected $listItems;

    protected $title;
}

The orderBy argument, category.title unfortunately will not work in doctrine. My understanding is that the most common solution is to store an extra property on the ListItem Entity such as $categoryTitle and using this new field in the orderBy annotation. For example;

class List
{
    /**
     * @ORM\OneToMany(targetEntity="ListItem", mappedBy="list")
     * @ORM\OrderBy({"categoryTitle" = "ASC"})
     */
    protected $listItems;
}

class ListItem
{
    // --

    protected $categoryTitle
}

The problem with this approach is the extra overhead of keeping this $categoryTitle, up to date through set methods and/or listeners, and obviously the denormalization of database data.

Is there a method I can use to order this association with doctrine, without degrading the quality of my database?

Answer 1

To solve this issue, I added the following method to the abstractEntity that all of our entities extend, and therefore all entities can sort their collections.

The following code has hasn't under gone any tests, but it should be a good starting point for anyone that might have this issue in future.

/**
 * This method will change the order of elements within a Collection based on the given method.
 * It preserves array keys to avoid any direct access issues but will order the elements
 * within the array so that iteration will be done in the requested order.
 *
 * @param string $property
 * @param array  $calledMethods
 *
 * @return $this
 * @throws \InvalidArgumentException
 */
public function orderCollection($property, $calledMethods = array())
{
    /** @var Collection $collection */
    $collection = $this->$property;

    // If we have a PersistentCollection, make sure it is initialized, then unwrap it so we
    // can edit the underlying ArrayCollection without firing the changed method on the
    // PersistentCollection. We're only going in and changing the order of the underlying ArrayCollection.
    if ($collection instanceOf PersistentCollection) {
        /** @var PersistentCollection $collection */
        if (false === $collection->isInitialized()) {
            $collection->initialize();
        }
        $collection = $collection->unwrap();
    }

    if (!$collection instanceOf ArrayCollection) {
        throw new InvalidArgumentException('First argument of orderCollection must reference a PersistentCollection|ArrayCollection within $this.');
    }

    $uaSortFunction = function($first, $second) use ($calledMethods) {

        // Loop through $calledMethods until we find a orderable difference
        foreach ($calledMethods as $callMethod => $order) {

            // If no order was set, swap k => v values and set ASC as default.
            if (false == in_array($order, array('ASC', 'DESC')) ) {
                $callMethod = $order;
                $order = 'ASC';
            }

            if (true == is_string($first->$callMethod())) {

                // String Compare
                $result = strcasecmp($first->$callMethod(), $second->$callMethod());

            } else {

                // Numeric Compare
                $difference = ($first->$callMethod() - $second->$callMethod());
                // This will convert non-zero $results to 1 or -1 or zero values to 0
                // i.e. -22/22 = -1; 0.4/0.4 = 1;
                $result = (0 != $difference) ? $difference / abs($difference): 0;
            }

            // 'Reverse' result if DESC given
            if ('DESC' == $order) {
                $result *= -1;
            }

            // If we have a result, return it, else continue looping
            if (0 !== (int) $result) {
                return (int) $result;
            }
        }

        // No result, return 0
        return 0;
    };

    // Get the values for the ArrayCollection and sort it using the function
    $values = $collection->getValues();
    uasort($values, $uaSortFunction);

    // Clear the current collection values and reintroduce in new order.
    $collection->clear();
    foreach ($values as $key => $item) {
        $collection->set($key, $item);
    }

    return $this;
}

This method then could then be called somewhat like the below to solve the original question

$list->orderCollection('listItems', array('getCategory' => 'ASC', 'getASecondPropertyToSortBy' => 'DESC'))