1

Im trying to get a resultset of 3 tables (messages, categories, shops) in the querybuilder of Doctrine(2.6.1).Im using WHERE on two joined tables (categories and shops).When i use setMaxResults()on messages it also affects the joined tables (categories) but i want all the categories of each message.(shops is unnesseary in this case).

I already tried it with a subquery but im getting always a result which limits the categories of the messages.

what ive tried:

    public function getListByShopIdAndCategoryIdQuery($shopId, $categoryId, $offset){
    $catQueryBuilder = ...

    $catQueryBuilder->select("ma.id")
        ->from($this->getEntityName(), "ma")
        ->innerJoin("ma.categories", "ca")
        ->where("ca.id = :catId");


    $builder = ...

    $builder->select(["m", 'c'])
        ->from($this->getEntityName(), "m")
        ->join('m.shops', 's')
        ->join('m.categories', 'c')
        ->where($builder->expr()->in('m.id', $catQueryBuilder->getQuery()->getDQL()))
        ->andWhere("s.id=:shopId")
        ->setParameter("catId", $categoryId)
        ->setParameter("shopId", $shopId)
        ->setMaxResults(2)
    ;

    return $builder->getQuery();
}

The attribute "shops" in the model "message" is set to ManyToMany, The attribute "categories" in the model "message" is set to ManyToMany too.

christian
  • 11
  • 2
  • Use a paginator http://docs.doctrine-project.org/projects/doctrine-orm/en/latest/tutorials/pagination.html https://stackoverflow.com/questions/5620771/limiting-a-doctrine-query-with-a-fetch-joined-collection – Jannes Botis Feb 27 '18 at 22:12

0 Answers0