Doctrine Optimize ManyToMany Queries
I got the following Entities:
- A User Entity, it has OneToMany Products and OneToMany Images
- A Product Entity that has ManyToMany Images
- An Image Entity, having the ManyToOne to User, and the (inverted) ManyToMany Products
The Images connected to the Product are also always connected to the same User Entity too.
I want to display for the Current User the Products and their Images in a Table.
- For this, the User is loaded by the User Provider. (1 Query)
- Then it gets the N Products for the User (1 Query)
- Then it ask Images joined by the ManyToMany table which Images are connected to the Products (N Queries)
The Last Queries I want to optimize somehow if able. My thought because I already know what kind of Images there will be because of the User OneToMany Relationship.
What could I do it make it better? Try to build my own Query? My own Hydrator? Maybe Caching of the Images Entities will be enough?
I tried making the Entities and their relationships Cacheable but that doesn't seems to reduce the amount of queries.
From what you explain:
You need to load from both user side (product and image). It can happen to load image not linked to the user (and product not linked to the user).
Your problem com from the many to many between product and image, that will force you to load image from product and not from user.
The solution would be to change your database to match this:
where the linked table containing all 3 foreign key has a uniq constraint on all 3. This way, you just have to load everything in 1 query from user if you use DQL or SQL.
- What is the difference between WITH and ON in Doctrine Join condition type?
- What's the difference between getScalarResult and getArrayResult in Doctrine
- Is there a way to enforce a unique column using doctrine2?
- Doctrine returns Entity deleted via onDelete: 'cascade'
- Doctrine ghost/proxy objects still getting after disabling enable_lazy_ghost_objects and auto_generate_proxy_classes
- How to generate db column type "text" with doctrine migrations?
- How to use more advanced parameters on Doctrine query builder's 'orderBy()'?
- Zenstruck/Foundy doesn't set the foreign key when loading the fixtures
- How to use sqlite database on symfony2 project?
- Best practice for mongodb bulk inserts in Symfony2
- Doctrine: How should I update my entity-saving function?
- Using Redis on Azure with Symfony2
- Join columns by referencedColumnName that is not primary key - id
- Persisting other entities inside preUpdate of Doctrine Entity Listener
- How do I autoload Doctrine entities mappings in a Symfony compiler pass?
- Doctrine2 challenge, I Have OneToMany, but NOT ManyToOne,
- What are Collections and ArrayCollections in entities?
- INET_ATON in where statement doctrine2 querybuilder zend2
- Cached keys as "Array" or "Object" - Laminas Cache with Doctrine (PHP8.3)
- Query m:n relationship for n = null - returns Lexer error
- Doctrine entitymanger not inserting ID column
- Update failure in v10, MYSQLI_SERVER_PUBLIC_KEY
- Doctrine entity object to array
- Doctrine2.1: Finding by DiscriminatorColumn results in "Unknown field" exception
- findBy with multiple IDs
- The class 'App\Repository\Users\UserRepository' was not found in the chain configured namespaces App\Entity\Users
- Can Doctrine define MySQL's Generated columns?
- Default value in Doctrine
- Symfony3, Doctrine2, custom fixtures loader
- How to set auto_generate_proxy_classes to AUTOGENERATE_EVAL when using Doctrine with Symfony?