search

propelorm / Propel2

Propel2 is an open-source high-performance Object-Relational Mapping (ORM) for modern PHP
http://propelorm.org/
MIT License
1.26k stars 399 forks source link

Many-to-many eager hydration (feature request) #1965

Open pine3ree opened 1 year ago

pine3ree commented 1 year ago

Suppose we have a User to Role m:m relationship via a cross-reference table user_to_role. It would be useful to be able to write:

$users = UserQuery::create()->with('Role')->find();

and only have 1 extra query to fetch related roles records (and hydrate them), instead of issuing 1 query per User entity. We would need a UserToRoleQuery::filterByUserIDs([...]). It would also be useful to be able to reference a many-to-many relation via plural name like ->with('roles').

kind regards

mringler commented 1 year ago

Have a look at the documentation on minimizing queries here and here, the latter also gives an example on how to use populateRelation() on result sets.

pine3ree commented 1 year ago

Hello @mringler ,

I tried using join+with and joinWith, and I also tried with('User.UserToRole') + with('UserToRole.Role'), but it does not seem to work for many-to-many. When I loop over the users in the example above, I still see 1-query per user to fetch the related roles. I totally missed the populateRelation method. I'll have a look at it. Still, a ->with('roles') call (with pluralization for related AR) would be easier to understand.

kind regards

pine3ree commented 1 year ago

Hello @mringler,

I checked populateRelation but it only supports RelationMap::ONE_TO_MANY and RelationMap::MANY_TO_ONE (https://github.com/propelorm/Propel2/blob/011aaa77d09fd5357f17b60fa3c5ba488c1690ae/src/Propel/Runtime/Collection/ObjectCollection.php#L406)

pine3ree commented 1 year ago

Hello @mringler,

commenting my opening issue: actually a UserToRoleQuery::filterByUserId([...]) method is created and it supports an array of user-id translated into a IN sql condition. But it is not used to hydrate a many-to-many relation.