Doctrine 2.3 Criteria. Accessing a related Object

Question

I am trying to set up a Criteria according to the Doctrine Docs.

Unfortunately they don't tell you how to access attributes of an related Object. Let me give you an example.

I have an ArrayCollection of Products. Every Product has a Category. I want to filter the ArrayCollection for a Category Name. Now I am trying to set up a Criteria as follows:

$criteria = Criteria::create()
  ->where(Criteria::expr()->eq("category.name", "SomeCategoryName"));

Now I get the following Exception:

An exception has been thrown during the rendering of a template ("Unrecognized field: category.name")

How can I access a related Object?

Sunshine

Answer 1

I looked into the source code Criteria::expr()->eq("name", --- second value ---). Second value expects an instance of Doctrine\Common\Collections\Expr\Value. So it's not possible to put another Expr or criteria in there. Only the Expr And and Or take another Expr. I'm pretty sure you are suppose to solve this with other functions like filter() or get an iterator with getIterator(). This is how it can be done with the filter() method.

$filteredProducts = 
    $products->filter(function($key, $element) use ($categoryName) {
        return $element->getCategory()->getName() === categoryName;
    });

If you can an Iterator for each next relation you can nest foreach loops and filter inside those.

Wordsmith

Thank you for your answer. I will test your Ctriteria on monday. Right now I don't have access to my workstation. I will let you know if it works. However filter is a bad idea in my opinion, because all Categories will be eager loaded and then processed locally on the disk (What will never be as perfomant than doing it on a DB). – Sunshine

You are misinformed. Criteria can be used in a query (data to be fetched) as well as on an ArrayCollection (already fetched data). If you didn't make fetch-joins the relation will be eager loaded whether you use criteria or filter(). You are right that the DB gives you most performance so it's best to use fetch join in combination with WHERE. Though that was not what your question was about. – Wordsmith

I thought it would be possible to annotate your entities, so that they are lazy loaded? docs.doctrine-project.org/en/2.0.x/reference/…. Wouldn't that load the ArrayCollection lazy? Or wouldn't that work because that's the inverse Annotation? – Sunshine

They are lazy loaded by default if you don't specify the annotation for it. However when you are using criteria (with the matching() method) on a non-retreived ArrayCollection it will start to lazy load just as well as it would with filter(). The owning/inverse possibility is unrelated to this. – Wordsmith

matching method with Criteria loads only those records that satisfy that criteria. filter method forces full collection load. – Sapajou

Yet super useful – Amie

Answer 2

That probably belongs in a repository method, rather than a filter method. If you're wanting to get a pre-filtered list of Products in a collection on a parent object (like an Order or something), you can filter the child collection in the query builder. However, you have to deal with the possibly confusing side-effect of not having fully hydrated objects.

This should give you a list of Order objects, which only having Product children matching a category name.

class OrderRepository extends EntityRepository {
  public function findOrderWithProductCategory($category)
  {
    $builder = $this->createQueryBuilder('o')
      ->select('o, p')
      ->leftJoin('o.products', 'p')
      ->join('p.category', 'c', 'WITH', 'c.name = :category')
      ->setParameter('category', $category);
  }
}

---

If you don't know what kind of categories you're interested until later, then you're probably better using @Flip's solution anyway, and pre-hydrating all the categories. Using partial hydration and standard ArrayCollection::filter() closures, performs pretty well in most cases.

That said, it would be quite nice as a feature. I suspect the Doctrine guys would be reluctant because the current Criteria implementation is very light-weight and they probably want to keep it that way.

Majorette

indeed, this issue is already opened: – Abundance

Answer 3

As noted in this comment https://github.com/doctrine/orm/issues/2918#issuecomment-2008389326 the Criteria matching nested properties is already working on ArrayCollections but not on PersistentCollections (yet) (as stated here https://mcmap.net/q/517803/-symfony-arraycollection-vs-persistentcollection PersistentCollections are Lazy).

You might want to use a criteria in multiple places and not using repositories methods or array_filters, so a workaround (with a memory tradeoff) is to load the "categories" (in the op case) beforehand, something like :

$criteria = Criteria::create()
  ->where(Criteria::expr()->eq("category.name", "SomeCategoryName"));
foreach ($this->getCategories() as $category) {
   // do  nothing but loading categories
}
return $this->categories->matching($criteria); // $this->categories is now an ArrayCollection

Cockroach