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(); } }