Doctrine 2, link sampling optimization

Question

PHP

Hello.

I use Doctrine 2. There was a problem.

There are such entities:

/**
 * @Entity
 * @Table(name="users")
 */
class User {

    /**
     * @Column
     * @Id
     */
    public $user_id;

    /**
     * @OneToMany(targetEntity="Post", mappedBy="user",fetch="EAGER")
     */
    public $posts;
}

/**
 * @Entity
 * @Table(name="posts")
 */
class Post {

    /**
     * @Column
     * @Id
     */
    public $post_id;

    /**
     * @ManyToOne(targetEntity="User", inversedBy="posts")
     * @JoinColumn(name="user_id", referencedColumnName="user_id")
     */
    public $user;
}

If use

$users = $em->getRepository('User')->findAll();

THAT turns out to be very non-optimal, a lot of requests:

SELECT u0_.user_id AS user_id0 FROM users u0_

SELECT t0.post_id AS post_id1, t0.user_id AS user_id2 FROM posts t0 WHERE t0.user_id = ?

array(1) {
  [0]=>
  string(1) "1"
}
array(1) {
  [0]=>
  NULL
}
SELECT t0.post_id AS post_id1, t0.user_id AS user_id2 FROM posts t0 WHERE t0.user_id = ?

array(1) {
  [0]=>
  string(1) "2"
}
array(1) {
  [0]=>
  NULL
}
SELECT t0.post_id AS post_id1, t0.user_id AS user_id2 FROM posts t0 WHERE t0.user_id = ?

array(1) {
  [0]=>
  string(1) "3"
}
array(1) {
  [0]=>
  NULL
}

If using DQL

$users = $em
  ->createQuery('SELECT user,post FROM User user JOIN user.posts post')
  ->getResult();

It also turns out not very optimal.

    SELECT u0_.user_id AS user_id0, p1_.post_id AS post_id1, p1_.user_id AS user_id2 FROM users u0_ INNER JOIN posts p1_ ON u0_.user_id = p1_.user_id

because a lot of duplicate data

user_id0	post_id1	user_id2
one	one	one
one	2	one
2	3	2
2	4	2
2	5	2

The ideal would be:

SELECT u0_.user_id AS user_id0, p1_.post_id AS post_id1, p1_.user_id AS user_id2 FROM users u0_
SELECT t0.post_id AS post_id1, t0.user_id AS user_id2 FROM posts t0 WHERE t0.user_id IN (1,2,3)

But I don’t understand how to do it correctly, you can do it separately manually, but then how to merge the collections, so that Doctrine does not try to load the collection itself when merging.

It is possible, in theory, to remove Doctrine links and do it manually, but then standard things like saving cascade, saving links, lazy loading when necessary will stop working.

Wrote to the bug tracker, www.doctrine-project.org/jira/browse/DDC-1149?page=com.atlassian.jira.plugin.system.issuetabpanels%3Acomment-tabpanel

Wrote what seems to be optimized, but what, where and how it works , unclear.