Symfony 2.2，Doctrine 2：复杂的关系实体检索

Question

Background

I'm working on a portfolio website, the website is fairly simple, the bulk of the work is on the gallery. I have a a set of database tables all linked up to retrieve and filter galleries in different ways, from the top, it is as follows:

GalleryCategory -> Gallery -> GalleryImage

---

The Problem

The problem I'm facing is only on gallery category pages, where I'm looking at all of the galleries in a given category, and then, the gallery images within the galleries returned.

My controller currently looks like this:

public function galleryCategoryAction($categoryId)
{
    $em = $this->getDoctrine()->getManager();

    $category  = $em->getRepository('SeerUKDWrightGalleryBundle:GalleryCategory')->findById($categoryId);
    $galleries = $category->getGalleries();

    $galleryImages = [ ];
    foreach ($galleries as $i => $gallery)
    {
        $galleryImages[$gallery->getId()] = $em->getRepository('SeerUKDWrightGalleryBundle:GalleryImage')
            ->findOneByGalleryId($gallery->getId());
    }

    return $this->render('SeerUKDWrightGalleryBundle:Gallery:category.html.twig', array(
        'category'      => $category,
        'galleries'     => $galleries,
        'galleryImages' => $galleryImages
    ));
}

Now, as much as this does work, exactly how I want it to, I feel like it's just... wrong! Getting the galleries in a category is nice and clean:

$category  = $em->getRepository('SeerUKDWrightGalleryBundle:GalleryCategory')->findById($categoryId);
$galleries = $category->getGalleries();

But then the only way I see to get the first image of each gallery is to loop over them and retrieve each entity. Like I said, this just seems wrong.

The only other suggestion to improve this that I've had is to use the entity manager in the gallery entity to get images within the entity, this of course would defeat the very purpose that Doctrine is meant to be used for.

---

Conclusion

In conclusion, how do I do this better? There must be a place to do this better and I'm just new to Symfony. Looking over the documentation hasn't helped...

---

Additional Details

I forgot to mention, the ability to still be able to use the entities is a necessity, I have some methods on the entities that are used later on. For example, in the GalleryImage entity I have methods to get the system path and web path of the image. I'll still need to be able to use those.

Answer 1

There is a bunch of things you can do; I'll just make a list of bullet points so you can discover them.

• Use the ArrayCollection object; it's just awesome (supports callback on some of its functions)
• Use custom repository classes; it will help you organize your code (I like to have all my queries related to an entity in one place).
• Use the query builder and join related entities to avoid extra queries when data is lazy-loaded. (more info on that below)

The lazy-loading will generate extra queries; if you know you are going to pull a collection of associated entities you probably need to join these entities.

The following code generates two queries

$category  = $em->getRepository('SeerUKDWrightGalleryBundle:GalleryCategory')->findById($categoryId);
$galleries = $category->getGalleries();

If you follow the documentation you should do the following (preferably in the entity's repository class)

$em = $this->getDoctrine()->getManager();

$query = $em->createQuery(
    'SELECT gc, g
     FROM SeerUKDWrightGalleryBundle:GalleryCategory gc
     LEFT JOIN gc.galleries g
     WHERE gc.id = :categoryId'
)->setParameter('categoryId', $categoryId);

$category = $query->getResult();

That's only one query.

If you have a lot of galleries, let's say 100,000, your server will quickly run out of memory so you might want to paginate or use lazy-loading with the extra lazy loading option.

If you want to abstract your code even more you can add services between your repositories and your controllers.

• Repositories would get the data from database
• Services would call the repositories, get the data and manipulate it (like a Helper class).
• Controllers would call the services and inject data into the templating layer (among other things).

If you want to get the first image of each gallery, you can create a SELECT query with a IN statement and a GROUP BY statement. That way you do one query of all galleries rather than 'x' queries (100,000 queries if there are 100,000 galleries).