This question relates a bit to https://groups.google.com/forum/#!searchin/doctrine-user/self$20referencing|sort:relevance/doctrine-user/WXhqjaZnc5g/ww3HUoBx6K8J, only now it is about:
- Organizations, which can have subOrganzations - Organizations can have Employees *1:m self-referencing:* http://doctrine-orm.readthedocs.org/en/latest/reference/association-mapping.html#one-to-many-self-referencing *1:m: * http://doctrine-orm.readthedocs.org/en/latest/reference/association-mapping.html#one-to-many-bidirectional Suppose I got an "Organization" Entity - which has a self-referencing one-to-many association: "subOrganizations" (children) - self referencing association: in this case a Organization.parent field is set, NULL for 'root' Organizations - each Organization can have (lots of) Employees: Organization.employees Now for a given Employee X, I would like to find all Organizations with which they are related, including the subOrganizations. To be able to apply pagination at database level, the result should be a flattened Collection of Organizations (and not a hierarchy): the LIMIT and OFFSET should be able to be done at database level and not in userland code (merging all the subOrganizations into the main result, since the amount of Organizations involved, this is a major performance issue). We can assume that: - Employees are only related to root Organizations - only one level of subOrganizations need to be respected What I got is (pseudo code): $queryBuilder ->addSelect('organization') ->addSelect('subOrganizations') //Probably not required, currently used to eagerly load the subOrganizations ->leftJoin('organization.subOrganizations','subOrganizations') ->where( $expressionBuilder->orX( $expressionBuilder->isMemberOf(':employee', 'organization.employees' ) $expressionBuilder->eq('subOrganizations.parentOrganization', 'organization'), ) ) ->setParameter(':serviceProviderUser', $serviceProviderUser->getId()) ->setMaxResults($limit) ->setFirstResult($offset); This works, but does not consider the Organizations as a 'flat' Collection, so setMaxResults() and setFirstResult() will not work properly. The only work around seems to process all the results, 'flatten' them manually and then apply the LIMIT / OFFSET constraints. In case an Employee is related to a large amount of Organizations, this takes a long time... Anybody got a way to fetch all the results as a non-nested / flattened collection of Organizations so no merging / flattening has to take place in PHP code? Cheers! -- You received this message because you are subscribed to the Google Groups "doctrine-user" group. To unsubscribe from this group and stop receiving emails from it, send an email to [email protected]. To post to this group, send email to [email protected]. Visit this group at https://groups.google.com/group/doctrine-user. For more options, visit https://groups.google.com/d/optout.
