Symfony 2 and Doctrine 2 - Using "fetch" joins returns empty array

Question

I have an Entity "BlogPosts" with yml mapping:

type: entity
table: blog_posts
id:
  id:
    type: integer
    generator: { strategy: AUTO }
fields:
  body:
    type: text
  created_at:
    type: datetime
manyToMany:
  replies:
    targetEntity: BlogPosts
    inversedBy: replyTo
    orderBy: {"id": "DESC"}
    orphanRemoval: true
    joinTable:
      name: blog_post_replies
      joinColumns:
        reply_to_id:
          referencedColumnName: id
      inverseJoinColumns:
        reply_id:
          referencedColumnName: id
  replyTo:
    targetEntity: BlogPosts
    mappedBy: replies
manyToOne:
  user:
    targetEntity: User
    inversedBy: posts
    joinColumns:
      user_id:
        referencedColumnName: id
        nullable: false

The problem is, when i try to fetch the data using a JOIN, the result is empty.

    $query = $em->createQuery("SELECT p, rl FROM <Bundle>:BlogPosts p JOIN p.replies rl WHERE p.user = 1");
    $posts = $query->getResult(); // Returns empty array

But it works perfectly without a join:

    $query = $em->createQuery("SELECT p FROM <Bundle>:BlogPosts p WHERE p.user = 1");
    $posts = $query->getResult(); // Works as intended

What am i missing? I am really desperate :(

EDIT: Just found out, that it returns only posts, that have replies. How can i select all posts and if it has replies, replies too?

Post you own solution as an answer. In this way when someone else has the same problem they can find the answer easily — Carlos Granados
– Carlos Granados, Commented Sep 20, 2012 at 17:31

Arthur · Accepted Answer · 2012-09-23 15:47:29Z

1

So i found the problem! Instead of JOIN i used LEFT JOIN:

$query = $em->createQuery("SELECT p, rl FROM <Bundle>:BlogPosts p LEFT JOIN p.replies rl WHERE p.user = 1");
$posts = $query->getResult(); // Now it works like a charm!

So now MySQL joins the result to the blog posts! When using a simple JOIN, it tries to join the posts to the replies, in witch case the result will be empty if the post doesn't have any!

edited Sep 23, 2012 at 15:47

answered Sep 21, 2012 at 7:42

Arthur

1,4431 gold badge18 silver badges35 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Symfony 2 and Doctrine 2 - Using "fetch" joins returns empty array

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related