Symfony2, PHP decrease query weight when counting many objects from database

Question

So I have 4 tables in my database. Product, Category, ProductCategory and ProductSubcategory.

These tables are in a ManyToMany relationship.

All products are saved in table: Product.

All Categories and Subcategories are saved in one table: Category.

A Product can have a Subcategory without the Category, can have both, or can have a Category without any Subcategory.

ProductCategory and ProductSubcategory is holding all the relationships.(product_id and category_id/subcategory_id)

Now in my twig I want to count how many products does the category and subcategory hold. For that I am using Twig Extensions.

For Subcategory:

Counting how many products are in a subcategory is easy and it works.

In Twig:

{% for subcategory in categories.children %}

     [{{ getProductsBySubcategory(subcategory.id) }}]

{% endfor %}

The function:

public function getProductsBySubcategory($id)
    {
    $products = 0;

    $subcategories = $this->doctrine->getRepository('MpShopBundle:ProductSubcategory')->findBy(array('subcategory' => $id));

    foreach ($subcategories as $subcategory) {
        $products++;
    }

    return $products;

    }

For Category:

For category its a little bit different. Here I want to count all the products in the Category and all the products in Subcategories that this category has. First I check if the category has products and if it has I add the to the array. Then I check if this category has subcategories. If it does i get all the products from the subcategories and add them in to the same array. Now if the product is in both the category and subcategory i dont want to double count. Thats why I am storing everything in an array(to do array_unique later).

My Twig:

[{{ getProductsByCategory(categories.id)|length }}]

The function:

public function getProductsByCategory($id)
    {
    $category = $this->doctrine->getRepository('ApplicationSonataClassificationBundle:Category')->find($id);
    $productCategories = $this->doctrine->getRepository('MpShopBundle:ProductCategory')->findBy(array('category' => $id ));
    $productSubcategories = $this->doctrine->getRepository('MpShopBundle:ProductSubcategory')->findAll();
    $products = array();

    foreach($productCategories as $productCategory) {

        array_push($products, $productCategory->getProduct()->getId());
    }

    foreach($productSubcategories as $productSubcategory) {

       $subcategory = $productSubcategory->getSubcategory();

        if($subcategory->getparent() == $category) {
            array_push($products, $productSubcategory->getProduct()->getId());
        }

    }
        $result = array_unique($products);

    return $result;

    }

THE PROBLEM:

Everything works in local. I am getting the correct number. However when I switch to Live I get error:500. I am thinking that this is probably because Live has thousands of products and allot of categories. So the foreach loops are breaking the server. Or maybe its because i am saving everything in array?

What should I do? Is it the server problem and I need to increase something? Or maybe there is a way to count without using arrays and decreasing the loop count? But how to check for duplicates then?

The logs show this:

Allowed memory size of 536870912 bytes exhausted (tried to allocate 77 bytes) in /var/www/kd_cms/vendor/symfony/symfony/src/Symfony/Component/HttpKernel/DataCollector/DataCollector.php on line 40

Check your error logs, error 500 is a "Internal server error" — Epodax
I know. But I am only getting this if I am trying to count products. If I remove the count the server is back online. — Dominykas55
You need to check the log to have a more precise error message. Or install some extension like Elao to have errors notified by email. — Alessandro Lai
Instead of trying to count the categories and subcategories using PHP loops, use SQL queries (using Doctrine DQL language). It is more difficult if you are not an SQL expert, but it is the only way of getting results reasonably fast — Carlos Granados

rommct rommct · Accepted Answer · 2015-10-19T09:36:13

Dql is faster than foreach with large db objects

// .../ProductCatRepository.php
public function findDistinctProductsByCat($catId)
{
   $query = $this->createQueryBuilder('entity')
        ->leftjoin('entity.category', 'cat')  
        ->andWhere('cat.id = :cat')
        ->setParameter('cat', $catId)
    ;
    return $query->getQuery()->getResult();
}

// .../ProductSubCatRepository.php
public function findDistinctProductsByCatSubCat($catId)
{
   $query = $this->createQueryBuilder('entity')
        ->leftjoin('entity.subcategory', 'subcat') 
        // maybe missing a leftJoin with cat idk your mapping
        // ->leftJoin('subcat.category', 'cat')
        // ->andWhere('cat.parent = :cat')
        ->andWhere('subcat.parent = :cat')
        ->setParameter('cat', $catId)
    ;
    return $query->getQuery()->getResult();
}


public function getProductsByCategory($id)
{
$categoryId = $id; 

$productsCat = $this->doctrine->getRepository('MpShopBundle:ProductCat')->findDistinctProductsByCat($categoryId);
$productsSubCat = $this->doctrine->getRepository('MpShopBundle:ProductSubCat')->findDistinctProductsByCatSubCat($categoryId);
// ArrayCollection::count()

return array('count_cat' => $productsCat->count(), 'product_subcat' =>    $productsSubCat->count(), 'result' => array_unique(array_merge($productsSubCat, $productsCat)));
}

Symfony2, PHP decrease query weight when counting many objects from database

1 Answers