Reputation: 5237
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 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.
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...
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.
Upvotes: 3
Views: 960
Reputation: 6276
There is a bunch of things you can do; I'll just make a list of bullet points so you can discover them.
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.
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).
Upvotes: 6