How to OrderBy on OneToMany/ManyToOne

Question

I have a Product class that has many fields on it for ManyToMany, such as ingredients, sizes, species, etc.. A total of about 14 different fields Not all of the fields are are relevant to each product.

I have mapping set up like this

Class product {
/**
 * @var Species[]
 * @ORM\ManyToMany(targetEntity="Species")
 * @ORM\JoinTable(name="product_species",
 *      joinColumns={@ORM\JoinColumn(name="productId", referencedColumnName="id")},
 *      inverseJoinColumns={@ORM\JoinColumn(name="speciesId", referencedColumnName="id")}
 *      )
 * @ORM\OrderBy({"name" = "asc"})
 */
private $species;

This works great for a manytomany/manyto one.

The problem is in my product_ingredients table I needed to add an additional field, meaning need to switch from ManyToMany to a OneToMany/ManyToOne So like this

/**
     * @var ProductIngredient[]
     *
     * @ORM\OneToMany(targetEntity="ProductIngredient", mappedBy="product")
     * @ORM\JoinColumn(name="productId", referencedColumnName="id")
     */
    private $ingredients;

Now my ProductIngredient Entity Looks like this

 /**
     * @var IngredientType
     * @ORM\ManyToOne(targetEntity="IngredientType", fetch="EAGER")
     * @ORM\JoinColumn(name="ingredientTypeId", referencedColumnName="id")
     */
    private $ingredientType;


    /**
     * @var Ingredient
     *
     * @ORM\ManyToOne(targetEntity="Ingredient", inversedBy="products", fetch="EAGER")
     * @ORM\JoinColumns({
     *   @ORM\JoinColumn(name="ingredientId", referencedColumnName="id")
     * })
     */
    private $ingredient;

    /**
     * @var Product
     *
     * @ORM\ManyToOne(targetEntity="Product", inversedBy="ingredients")
     * @ORM\JoinColumns({
     *   @ORM\JoinColumn(name="productId", referencedColumnName="id")
     * })
     */
    private $product;

So in my product class for species I use the @ORM\OrderBy so that species is already ordered.. Is there a way I can somehow also do this for my ingredients field?

Or am I doing my logic wrong and these shouldn't even be fields on the product class and should just be looking up by the repository instead?

I was wanting it to be easy so I could loop through my objects like $product->getIngredients() instead of doing

$ingredients = $this->getDoctrine()->getRepository('ProductIngredient')->findByProduct($product->getId());

score 88 · Answer 1 · edited Jul 31 '23 at 15:41

88

In the Product entity you just need to add the orderBy to the ingredients relation

/**
 * @ORM\OrderBy({"some_attribute" => "ASC", "another_attribute" => "DESC"})
 */
private $ingredients;

With PHP 8 attributes:

#[ORM\OrderBy(['some_attribute' => 'ASC', 'another_attribute' => 'DESC'])]
private $ingredients;

edited Jul 31 '23 at 15:41

Full

455
4
13

answered Sep 19 '12 at 13:45

Andreas Linden

12,489
7
51
67

This throws an error (Unrecognized field: name) because "name" does not exist in ProductIngredient It is in Ingredient entity. – Kris Sep 19 '12 at 17:40
Can you give me an example? My product entity connects to ProductIngredient, which connects to Ingredient. I tried doing the orderby in the productingredient class but since it is a manytoone it is ignored – Kris Sep 19 '12 at 20:10
1

You might need to clear the cache after you do this too. I ran into this issue where it didn't actually apply the order by until after refreshing the cache: ``php console.php orm:clear-cache:metadata && php console.php orm:clear-cache:query && php console.php orm:clear-cache:result`` – grdaneault Jun 12 '15 at 19:28
As the names here make sense (`ingredientType` vs `ingredient_type`), I think it could be helpful to rearrange your snippet to comply with the OP's snippet – Peyman Mohamadpour Mar 07 '17 at 08:38
Is there a way to order by a joined column? For example if you had a collection of `Car` - each car is related to a `Model`. Would it be possible to use the annotation to order by `model.name` ? – tftd Apr 06 '17 at 04:31
With php8 atttributes it looks like this: `#[ORM\OrderBy(['sort' => 'ASC'])]` – qwertz Feb 12 '22 at 20:43

score 7 · Answer 2 · answered Sep 20 '12 at 02:27

Well I came up with a hackish way.. Since I really only care about the sort on output, I have made a basic twig extension

use Doctrine\Common\Collections\Collection;

public function sort(Collection $objects, $name, $property = null)
{
    $values = $objects->getValues();
    usort($values, function ($a, $b) use ($name, $property) {
        $name = 'get' . $name;
        if ($property) {
            $property = 'get' . $property;
            return strcasecmp($a->$name()->$property(), $b->$name()->$property());
        } else {
            return strcasecmp($a->$name(), $b->$name());
        }
    });
    return $values;
}

I would like to avoid this hack though and still would like to know a real solution

Love it, very handy to have something like this in case of those random times you want to order differently — Adam, Apr 16 '14 at 23:49

score 4 · Answer 3 · answered Apr 20 '13 at 09:21

4

You should use 'query_builder' option in your Form: http://symfony.com/doc/master/reference/forms/types/entity.html#query-builder The value of the option can be something like this:

function(EntityRepository $er) {
    return $er->createQueryBuilder('i')->orderBy('i.name');
}

Don't forget to add the "use" statement for EntityRepository

answered Apr 20 '13 at 09:21

Massimiliano Arione

2,422
19
40

1

`@ORM\OrderBy` didn't work for me as my relation is `ManyToOne` this is what I needed! AND I could also add my `WHERE` clause!!! Thanks – Chad Nov 08 '18 at 20:23

score 1 · Answer 4 · answered Apr 17 '18 at 12:20

1

If you use xml mapping, you could use

        <order-by>
            <order-by-field name="some_field" direction="ASC" />
        </order-by>

inside your <one-to-many> tag.

answered Apr 17 '18 at 12:20

user1077915

828
1
12
26

How to OrderBy on OneToMany/ManyToOne

4 Answers4

Linked

Related