Introduction
I am using:
- XAMPP with PHP v7.1.6
- Symfony v3.3.4
- Doctrine v2.5.4
- StofDoctrineExtensionsBundle [1] in order to manage Tree structure.
Setting up
To setup Tree structure I used documentation on Symfony.com [2] followed by documentation on GitHub [3]. Then I proceeded with tree setup - used tree entity from example [4] and used code in [5] to create a tree.
I did setup the tree structure (that represents categories) called Category. I added several custom fields to the tree: for example is_active that represents active categories.
At the moment
I am using separate queries to get all Categories and corresponding Item counts, but i would like to get this info in just one, combined, query.
Question
Is it possible to get all Categories and corresponding Item counts in one query using DQL? If so then how?
MY CODE
Getting all Categories example
public function getCategoryTreeNodeArrayByRootId($category_tree_root_id)
{
$em = $this->getEntityManager();
$query = $em
->createQueryBuilder()
->select('ct')
->from('AppBundle:Category', 'ct')
->where('ct.root = :root')
->setParameter('root', $category_tree_root_id)
->orderBy('ct.root, ct.lft', 'ASC')
->getQuery();
$query->setHint(\Doctrine\ORM\Query::HINT_INCLUDE_META_COLUMNS, true);
$build_my_tree = $query->getArrayResult();
return $build_my_tree;
}
Getting element Item count that corresponds to Category example
public function getItemCountsByCategory($in_stock = true)
{
$em = $this->getEntityManager();
if ($in_stock === true)
{
// atrod preču skaitu pa kategorijām
$query = $em->createQueryBuilder()
->select('(i.category) as category_id, COUNT(i.id) as record_count')
->from('AppBundle:Item', 'i')
->where('i.in_stock != 0')
->groupBy('i.category')
->getQuery();
}
else if ($in_stock === false)
{
// atrod preču skaitu pa kategorijām
$query = $em->createQueryBuilder()
->select('(i.category) as category_id, COUNT(i.id) as record_count')
->from('AppBundle:Item', 'i')
->where('i.in_stock == 0')
->groupBy('i.category')
->getQuery();
}
$query->setHint(\Doctrine\ORM\Query::HINT_INCLUDE_META_COLUMNS, true);
$item_counts = $query->getArrayResult();
return $item_counts;
}
My Category entity:
<?php
namespace AppBundle\Entity;
use Gedmo\Mapping\Annotation as Gedmo;
use Doctrine\ORM\Mapping as ORM;
use Symfony\Component\Validator\Constraints as Assert;
use Doctrine\Common\Collections\ArrayCollection as ArrayCollection;
/**
* @Gedmo\Tree(type="nested")
* @ORM\Table(name="category")
* use repository for handy tree functions
* @ORM\Entity(repositoryClass="AppBundle\Repository\CategoryRepository")
*/
class Category
{
/**
* @ORM\Column(type="integer")
* @ORM\Id
* @ORM\GeneratedValue
*/
private $id;
/**
* @ORM\Column(type="string", length=100)
*
* @var string
*/
private $category_name_lv;
/**
* @ORM\Column(type="boolean")
*/
private $is_active;
/**
* @Gedmo\TreeLeft
* @ORM\Column(type="integer")
*/
private $lft;
/**
* @Gedmo\TreeLevel
* @ORM\Column(type="integer")
*/
private $lvl;
/**
* @Gedmo\TreeRight
* @ORM\Column(type="integer")
*/
private $rgt;
/**
* @Gedmo\TreeRoot
* @ORM\ManyToOne(targetEntity="Category")
* @ORM\JoinColumn(referencedColumnName="id", onDelete="CASCADE")
*/
private $root;
/**
* @Gedmo\TreeParent
* @ORM\ManyToOne(targetEntity="Category", inversedBy="children")
* @ORM\JoinColumn(referencedColumnName="id", onDelete="CASCADE")
*/
private $parent;
/**
* @ORM\OneToMany(targetEntity="Category", mappedBy="parent")
* @ORM\OrderBy({"lft" = "ASC"})
*/
private $children;
/**
* One Category has Many Items.
* @ORM\OneToMany(targetEntity="Item", mappedBy="category")
*/
private $items;
public function __construct()
{
$this->items = new ArrayCollection();
}
/**
* Get id
*
* @return integer
*/
public function getId()
{
return $this->id;
}
/**
* Set categoryNameLv
*
* @param string $categoryNameLv
*
* @return Category
*/
public function setCategoryNameLv($categoryNameLv)
{
$this->category_name_lv = $categoryNameLv;
return $this;
}
/**
* Get categoryNameLv
*
* @return string
*/
public function getCategoryNameLv()
{
return $this->category_name_lv;
}
/**
* Set isFile
*
* @param boolean $isActive
*
* @return Category
*/
public function setIsActive($isActive)
{
$this->is_active = $isActive;
return $this;
}
/**
* Get isFile
*
* @return boolean
*/
public function getIsActive()
{
return $this->is_active;
}
/**
* Set lft
*
* @param integer $lft
*
* @return Category
*/
public function setLft($lft)
{
$this->lft = $lft;
return $this;
}
/**
* Get lft
*
* @return integer
*/
public function getLft()
{
return $this->lft;
}
/**
* Set lvl
*
* @param integer $lvl
*
* @return Category
*/
public function setLvl($lvl)
{
$this->lvl = $lvl;
return $this;
}
/**
* Get lvl
*
* @return integer
*/
public function getLvl()
{
return $this->lvl;
}
/**
* Set rgt
*
* @param integer $rgt
*
* @return Category
*/
public function setRgt($rgt)
{
$this->rgt = $rgt;
return $this;
}
/**
* Get rgt
*
* @return integer
*/
public function getRgt()
{
return $this->rgt;
}
/**
* Set root
*
* @param \AppBundle\Entity\Category $root
*
* @return Category
*/
public function setRoot(\AppBundle\Entity\Category $root = null)
{
$this->root = $root;
return $this;
}
/**
* Get root
*
* @return \AppBundle\Entity\Category
*/
public function getRoot()
{
return $this->root;
}
/**
* Set parent
*
* @param \AppBundle\Entity\Category $parent
*
* @return Category
*/
public function setParent(\AppBundle\Entity\Category $parent = null)
{
$this->parent = $parent;
return $this;
}
/**
* Get parent
*
* @return \AppBundle\Entity\Category
*/
public function getParent()
{
return $this->parent;
}
/**
* Add child
*
* @param \AppBundle\Entity\Category $child
*
* @return Category
*/
public function addChild(\AppBundle\Entity\Category $child)
{
$this->children[] = $child;
return $this;
}
/**
* Remove child
*
* @param \AppBundle\Entity\Category $child
*/
public function removeChild(\AppBundle\Entity\Category $child)
{
$this->children->removeElement($child);
}
/**
* Get children
*
* @return \Doctrine\Common\Collections\Collection
*/
public function getChildren()
{
return $this->children;
}
/**
* Add item
*
* @param \AppBundle\Entity\Item $item
*
* @return Category
*/
public function addItem(\AppBundle\Entity\Item $item)
{
$this->items[] = $item;
return $this;
}
/**
* Remove item
*
* @param \AppBundle\Entity\Item $item
*/
public function removeItem(\AppBundle\Entity\Item $item)
{
$this->items->removeElement($item);
}
/**
* Get items
*
* @return \Doctrine\Common\Collections\Collection
*/
public function getItems()
{
return $this->items;
}
/**
* toString
*
* @return string
*/
public function __toString()
{
return $this->getCategoryNameLv();
}
}
My Item entity:
<?php
namespace AppBundle\Entity;
use Doctrine\ORM\Mapping as ORM;
use Symfony\Component\Validator\Constraints as Assert;
/**
* @ORM\Entity(repositoryClass="AppBundle\Repository\ItemRepository")
* @ORM\Table(name="item")
*/
class Item
{
/**
* @ORM\Column(type="integer")
* @ORM\Id
* @ORM\GeneratedValue(strategy="AUTO")
*/
protected $id;
/**
* @ORM\Column(type="string", length=100)
*/
protected $unique_id;
/**
* @ORM\Column(type="string", length=100)
*/
protected $name_lv;
/**
* @ORM\Column(type="string", length=100)
*/
protected $name_ru;
/**
* @ORM\Column(type="string", length=200)
*/
protected $category_sub;
/**
* @ORM\Column(type="string", length=500)
*/
protected $category_full;
/**
* @ORM\Column(type="string", length=500)
*/
protected $link_lv;
/**
* @ORM\Column(type="string", length=500)
*/
protected $link_ru;
/**
* @ORM\Column(type="string", length=500)
*/
protected $local_image_small_url;
/**
* @ORM\Column(type="string", length=500)
*/
protected $local_image_big_url;
/**
* @ORM\Column(type="string", length=500)
*/
protected $local_image_big2_url;
/**
* @ORM\Column(type="string", length=500)
*/
protected $local_image_big3_url;
/**
* @ORM\Column(type="string", length=3000)
*/
protected $description_lv;
/**
* @ORM\Column(type="string", length=3000)
*/
protected $description_ru;
/**
* @ORM\Column(type="decimal", scale=2)
*/
protected $price;
/**
* @ORM\Column(type="integer")
*/
protected $in_stock;
/**
* @ORM\Column(type="boolean")
*/
protected $is_exclusive;
/**
* @ORM\Column(type="boolean")
*/
protected $is_new;
/**
* @ORM\ManyToOne(targetEntity="Day", inversedBy="items")
* @ORM\JoinColumn(name="day_id", referencedColumnName="id", onDelete="CASCADE")
*/
protected $day;
/**
* Many Items have One Category.
* @ORM\ManyToOne(targetEntity="Category", inversedBy="items")
* @ORM\JoinColumn(name="category_id", referencedColumnName="id", onDelete="CASCADE")
*/
private $category;
/**
* Get id
*
* @return integer
*/
public function getId()
{
return $this->id;
}
/**
* Set uniqueId
*
* @param string $uniqueId
*
* @return Item
*/
public function setUniqueId($uniqueId)
{
$this->unique_id = $uniqueId;
return $this;
}
/**
* Get uniqueId
*
* @return string
*/
public function getUniqueId()
{
return $this->unique_id;
}
/**
* Set nameLv
*
* @param string $nameLv
*
* @return Item
*/
public function setNameLv($nameLv)
{
$this->name_lv = $nameLv;
return $this;
}
/**
* Get nameLv
*
* @return string
*/
public function getNameLv()
{
return $this->name_lv;
}
/**
* Set nameRu
*
* @param string $nameRu
*
* @return Item
*/
public function setNameRu($nameRu)
{
$this->name_ru = $nameRu;
return $this;
}
/**
* Get nameRu
*
* @return string
*/
public function getNameRu()
{
return $this->name_ru;
}
/**
* Set categorySub
*
* @param string $categorySub
*
* @return Item
*/
public function setCategorySub($categorySub)
{
$this->category_sub = $categorySub;
return $this;
}
/**
* Get categorySub
*
* @return string
*/
public function getCategorySub()
{
return $this->category_sub;
}
/**
* Set categoryFull
*
* @param string $categoryFull
*
* @return Item
*/
public function setCategoryFull($categoryFull)
{
$this->category_full = $categoryFull;
return $this;
}
/**
* Get categoryFull
*
* @return string
*/
public function getCategoryFull()
{
return $this->category_full;
}
/**
* Set linkLv
*
* @param string $linkLv
*
* @return Item
*/
public function setLinkLv($linkLv)
{
$this->link_lv = $linkLv;
return $this;
}
/**
* Get linkLv
*
* @return string
*/
public function getLinkLv()
{
return $this->link_lv;
}
/**
* Set linkRu
*
* @param string $linkRu
*
* @return Item
*/
public function setLinkRu($linkRu)
{
$this->link_ru = $linkRu;
return $this;
}
/**
* Get linkRu
*
* @return string
*/
public function getLinkRu()
{
return $this->link_ru;
}
/**
* Set localImageSmallUrl
*
* @param string $localImageSmallUrl
*
* @return Item
*/
public function setLocalImageSmallUrl($localImageSmallUrl)
{
$this->local_image_small_url = $localImageSmallUrl;
return $this;
}
/**
* Get localImageSmallUrl
*
* @return string
*/
public function getLocalImageSmallUrl()
{
return $this->local_image_small_url;
}
/**
* Set localImageBigUrl
*
* @param string $localImageBigUrl
*
* @return Item
*/
public function setLocalImageBigUrl($localImageBigUrl)
{
$this->local_image_big_url = $localImageBigUrl;
return $this;
}
/**
* Get localImageBigUrl
*
* @return string
*/
public function getLocalImageBigUrl()
{
return $this->local_image_big_url;
}
/**
* Set localImageBig2Url
*
* @param string $localImageBig2Url
*
* @return Item
*/
public function setLocalImageBig2Url($localImageBig2Url)
{
$this->local_image_big2_url = $localImageBig2Url;
return $this;
}
/**
* Get localImageBig2Url
*
* @return string
*/
public function getLocalImageBig2Url()
{
return $this->local_image_big2_url;
}
/**
* Set localImageBig3Url
*
* @param string $localImageBig3Url
*
* @return Item
*/
public function setLocalImageBig3Url($localImageBig3Url)
{
$this->local_image_big3_url = $localImageBig3Url;
return $this;
}
/**
* Get localImageBig3Url
*
* @return string
*/
public function getLocalImageBig3Url()
{
return $this->local_image_big3_url;
}
/**
* Set descriptionLv
*
* @param string $descriptionLv
*
* @return Item
*/
public function setDescriptionLv($descriptionLv)
{
$this->description_lv = $descriptionLv;
return $this;
}
/**
* Get descriptionLv
*
* @return string
*/
public function getDescriptionLv()
{
return $this->description_lv;
}
/**
* Set descriptionRu
*
* @param string $descriptionRu
*
* @return Item
*/
public function setDescriptionRu($descriptionRu)
{
$this->description_ru = $descriptionRu;
return $this;
}
/**
* Get descriptionRu
*
* @return string
*/
public function getDescriptionRu()
{
return $this->description_ru;
}
/**
* Set price
*
* @param string $price
*
* @return Item
*/
public function setPrice($price)
{
$this->price = $price;
return $this;
}
/**
* Get price
*
* @return string
*/
public function getPrice()
{
return $this->price;
}
/**
* Set inStock
*
* @param integer $inStock
*
* @return Item
*/
public function setInStock($inStock)
{
$this->in_stock = $inStock;
return $this;
}
/**
* Get inStock
*
* @return integer
*/
public function getInStock()
{
return $this->in_stock;
}
/**
* Set isExclusive
*
* @param boolean $isExclusive
*
* @return Item
*/
public function setIsExclusive($isExclusive)
{
$this->is_exclusive = $isExclusive;
return $this;
}
/**
* Get isExclusive
*
* @return boolean
*/
public function getIsExclusive()
{
return $this->is_exclusive;
}
/**
* Set isExclusive
*
* @param boolean $isNew
*
* @return Item
*/
public function setIsNew($isNew)
{
$this->is_new = $isNew;
return $this;
}
/**
* Get isExclusive
*
* @return boolean
*/
public function getIsNew()
{
return $this->is_new;
}
/**
* Set day
*
* @param \AppBundle\Entity\Day $day
*
* @return Item
*/
public function setDay(\AppBundle\Entity\Day $day = null)
{
$this->day = $day;
return $this;
}
/**
* Get day
*
* @return \AppBundle\Entity\Day
*/
public function getDay()
{
return $this->day;
}
/**
* Set category
*
* @param \AppBundle\Entity\Category $category
*
* @return Item
*/
public function setCategory(\AppBundle\Entity\Category $category = null)
{
$this->category = $category;
return $this;
}
/**
* Get category
*
* @return \AppBundle\Entity\Category
*/
public function getCategory()
{
return $this->category;
}
}
Conclusion
Please advise.
Thank you for your time and knowledge.
