Doctrine2 query from record

Question

I have a model where categories have children and parents. Products a re linked to a category. I would like to retrieve a list of products from the children of a certain category. I thought of doing something similar to doctrine1 in my templates:

{% for category in productsByCategories %}
    <h2>{{ category.label }}</h2>
    <ul class="products-list">
    {% for product in category.getLatestProductFromChildCategories() %}

However i don't know how to do it as i would need to pass the category repository object to my category object, and i am sure this is not a good idea.

Generally, how would i query from the category object (similar to how we were doing in records in doctrine1)?

Thanks !

paulgv · Accepted Answer · 2014-12-13T23:24:44.497

1

Would something like this achieve what you want ?

Twig

{% for category in productsByCategories %}
    <h2>{{ category.label }}</h2>
    <ul class="products-list">
    {# Loop through child categories #}
    {% for child in category.children %}
        {# Get products from the current child category #}
        {% for product in child.latestProducts %}
            <li>{{ product }}</li>
        {% endfor %}
    {% endfor %}
{% endfor %}

Category.php

<?php
// ...
public function latestProducts() {
    $length = 10;
    if ($this->products->count() < $length) $length = $this->products->count();
    $offset = $this->products->count() - $length;
    return $this->products->slice($offset, $length);
}
// ...

I guess you could also try querying the latest products in your controller.

Controller.php

<?php
public function showAction() {
    // ...
    $em = $this->getDoctrine()->getManager();
    // Get the main categories, then loop through them
    foreach ($categories as $category) {
        $childrenIds = array();
        foreach ($categories->getChildren() as $child) {
            array_push($childrenIds, $child->getId());
        }
        // Get the latest products using DQL
        $products = $em->createQuery('SELECT p FROM Application\ProductBundle\Entity\Product p WHERE p.category_id IN (?1) ORDER BY date_add DESC')
                        ->setParameter(1, $childrenIds)
                        ->setMaxResults(10);
        $category->setLatestProducts($products);
    }
    // ...
    return $this->render($template, array(
        'productsByCategories' => $categories
    ));
}

Category.php

<?php
protected $latestProducts;

public function getLatestProducts() {
    return $this->latestProducts;
}

edited Dec 13 '14 at 23:24

answered Dec 13 '14 at 22:36

paulgv

1,818
16
20

Thanks, almost but not quite ;) i am trying to retrieve the latest 10 products avaliable in all subcategories. something like 'SELECT * from products WHERE category_id IN (1,2,3) order by date_add DESC LIMIT 10 – poulping Dec 13 '14 at 22:38
i think the problem is that it loops on the categories, it will first loop on the child categories, and for each of them get the latest. However, i am not going to get the products ordered from latest globally. I believe however you got me on the right track: i should get all products from all child categories and rearrange them by product date. – poulping Dec 13 '14 at 22:56
I edited my answer with another suggestion, I'm really not sure about the DQL query and it may not be the cleanest solution but it might give you some ideas, I hope so at least :D – paulgv Dec 13 '14 at 23:14
Thanks for the suggestion. This is more or less the same thing as before though, i am trying not to loop on the child categories to get the products as in your case products are ordered by child category first, then product date. What i am trying to achieve is to find the products accross any all subcategories sorted by product date. – poulping Dec 13 '14 at 23:18
Well, here goes another edit :) I modified the controller part. – paulgv Dec 13 '14 at 23:25
That looks like it would work ! I will wait a little if there are other suggestions, but i like your idea, thanks ! – poulping Dec 13 '14 at 23:28
Good luck then ! :) Also, here is a discussion on how to use a Doctrine repository from a Twig template, you might find it useful : http://stackoverflow.com/questions/8450465/fetching-data-through-a-custom-repository-in-a-twig-extension – paulgv Dec 13 '14 at 23:30

Doctrine2 query from record

1 Answers1