public function getChildrenDirectByCategorie(Categorie $categorie = null)
{
$query = $this->createQueryBuilder('c');
if ($categorie != null) {
$query->where('c.lvl = ' . ($categorie->getLvl() + 1))->andWhere('c.rgt < ' . $categorie->getRgt())->andWhere('c.lft > ' . $categorie->getLft())->andWhere('c.root = ' . $categorie->getRoot());
} else {
$query->where('c.lvl = 0');
}
$query->orderBy('c.lft')->leftJoin('c.children', 'children')->addSelect('children')->leftJoin('children.lastPost', 'p')->addSelect('p');
return $query->getQuery()->getResult();
}
public function getLastPost(Categorie $c)
{
$lastDates = $this->createQueryBuilder('p')->join('p.mainCategorie', 'c')->addSelect('c')->addSelect('MAX(p.lastComment) as max_lastComment')->where('c.lft <= ' . $c->getLft() . ' and c.rgt >= ' . $c->getRgt() . ' and c.root = ' . $c->getRoot())->andWhere('p.publie = 1')->andWhere('p.datePublication < :today')->groupBy('p.mainCategorie')->setParameter('today', new \DateTime())->getQuery()->getArrayResult();
$where = "";
$i = 0;
$parameters = array();
foreach ($lastDates as $p) {
$i++;
$where .= " or p.lastComment = :p" . $i;
$parameters['p' . $i] = $p['max_lastComment'];
}
if (strlen($where) <= 0) {
return array();
} else {
$where = substr($where, 4);
$query = $this->createQueryBuilder('p')->join('p.mainCategorie', 'c')->addSelect('c')->where($where)->getQuery();
return $query->setParameters($parameters)->getResult();
}
}
