0

I am new to symfony so sorry if this is something really simple to anwer.

For the sake of example I have rewritten code snippets as if I was writing a blog.
I have a BlogPost entity with collection of BlogComments annotated like this:

 /**
 * @OneToMany(targetEntity="BlogComment", mappedBy="post")
 */
private $comments;

From my amateur point of view, Doctrine likes to work with complete objects, so this collection is either not initialized, or lazily loaded whenever I use the reference to it.

I guess you can imagine the overhead and memory requirements when every one of my BlogPosts has at least 500 Blogcomments and they all get initialized whenever I touch $comments variable.

What I am trying to achieve is to list ie. 50 blog posts, each with 20 latest comments (without the memory explosion). Additionally, I would like to be able to display only top 20 comments with most "likes" (or generally just select a subset based on some criteria).

Is there any generally recommended and clean way to achieve this kind of functionality? And when I achieve this, isn't use of such "incomplete" or "modified" entities going to break my logic (when updating/deleting items from the subset and persisting it)? I assume that solution to this will likely be a method in a custom repository, but I still can't see the thought behind it.

In advance, thank you for answers, I am really curious what kinds of solutions you will be able to come up with.

Jogurtoz
  • 1
  • 2

1 Answers1

0

Doctrine is not good to deal with a big list objets. It will be far slower than a classic SQL query followed by while ($row = $stmt->fetch()). Sometimes, it's better to perform native queries. 50 blogs posts * 20 comments = 1000 objects populated, and even probably more if you get the User name for each result.

You will need to use a native query anyway, because I don't think you can get your 20 comments per blog post with a pure DQL query. You will need to do some joined subquery to limit to 20 comments, see this post for more information: MySQL JOIN with LIMIT 1 on joined table

Once you have done your native query, and if you really need your objects, you can bind your results to Doctrine entities with the ResultSetMappingBuilder: http://doctrine-orm.readthedocs.org/projects/doctrine-orm/en/latest/reference/native-sql.html#resultsetmappingbuilder

Community
  • 1
  • 1
Raphaël Malié
  • 3,912
  • 21
  • 37
  • Thank you for your answer, I will definitely look into that. I can write the query in SQL without a problem. :) My other idea was to call another repository to fetch `BlogComment`s for the post and then manually assign them to `$comments` variable for each `BlogPost`...is that a wrong way of thinking in symfony/doctrine? – Jogurtoz Jan 28 '16 at 19:43
  • It's not a wrong way, but I would be really better to do it with only one good query : less work for your database, and better possibility to use a cache on the Doctrine side. In term of Doctrine, one query or several won't change a lot of thing, since its work is to take a row (an array) and transform it into a PHP object. He don't really cares if you do it with one or several queries. – Raphaël Malié Jan 28 '16 at 20:00