Thanks again Marco, that looks exactly like what I need :) On Thursday, May 29, 2014 7:28:09 PM UTC-5, Marco Pivetta wrote: > > Hi Jessica, > > Sorry, completely misunderstood the problem in first place. > > Setting a limit on a query with fetch-joined results can only be done > safely with the paginator: > http://docs.doctrine-project.org/projects/doctrine-orm/en/latest/tutorials/pagination.html > > > > Marco Pivetta > > http://twitter.com/Ocramius > > http://ocramius.github.com/ > > > On 29 May 2014 14:18, Jessica Mauerhan <[email protected] <javascript:> > > wrote: > >> Hi Marco, thanks so much for the reply - unfortunately I still only get >> one course instead of all of them. I also tried it using just the query as >> you wrote it instead of converting it to the query builder, same result - >> only 1 course. Here is my QueryBuilder code. >> >> $qb = $this->_em->createQueryBuilder(); >> $q = $qb->select('category', 'courses') >> ->from('models\Category', 'category') >> ->innerJoin('category.courses', 'courses') >> ->innerJoin('category.courses', 'courses_count') >> ->groupBy('category.id', 'courses.id') >> ->having('COUNT(courses_count.id) > 1'); >> return $q->getQuery()->setMaxResults(1)->getOneOrNullResult(); >> >> >> >> On Tuesday, May 27, 2014 7:18:39 PM UTC-5, Jessica Mauerhan wrote: >>> >>> I have the following two entities, Courses and Categories. A course can >>> be in multiple categories, and obviously a category can have multiple >>> courses. So I have mapped this as a many-to-many. >>> >>> (I have removed much of the irrelevant code) >>> >>> *Category Model:* >>> ** >>> * models\Category >>> * >>> * @ORM\Table(name="item_category") >>> * @ORM\Entity(repositoryClass="models\repositories\CategoryRepository") >>> */ >>> >>> class Category{ >>> >>> /** >>> * @var integer $id >>> * >>> * @ORM\Column(name="id", type="integer", nullable=false) >>> * @ORM\Id >>> * @ORM\GeneratedValue(strategy="IDENTITY") >>> */ >>> protected $id; >>> >>> /** >>> * @var string $name >>> * >>> * @ORM\Column(name="name", type="string", length=100, >>> nullable=false) >>> * @Assert\NotBlank() >>> */ >>> protected $name; >>> >>> /** >>> * @var ArrayCollection >>> * >>> * @ORM\ManyToMany(targetEntity="models\Course") >>> * @ORM\JoinTable(name="item_category_assignment", >>> * inverseJoinColumns={@ORM\JoinColumn(name="item_id", >>> referencedColumnName="id")}, >>> * joinColumns={@ORM\JoinColumn(name="item_category_id", >>> referencedColumnName="id")} >>> * ) >>> */ >>> protected $courses; >>> >>> public function __construct() >>> { >>> $this->courses = new ArrayCollection(); >>> } >>> } >>> >>> *Course Model:* >>> >>> /** >>> * models\Course >>> * >>> * @ORM\Table(name="item") >>> * @ORM\Entity(repositoryClass="models\repositories\CourseRepository") >>> */ >>> >>> class Course{ >>> >>> /** >>> * @var integer $id >>> * >>> * @ORM\Column(name="id", type="integer", nullable=false) >>> * @ORM\Id >>> * @ORM\GeneratedValue(strategy="IDENTITY") >>> */ >>> protected $id; >>> >>> /** >>> * @var string $name >>> * >>> * @ORM\Column(name="name", type="string", length=100, >>> nullable=false) >>> * @Assert\NotBlank() >>> */ >>> protected $name; >>> >>> /** >>> * @var ArrayCollection >>> * @ORM\ManyToMany(targetEntity="models\Category", >>> cascade={"persist"}) >>> * @ORM\JoinTable(name="item_category_assignment", >>> * joinColumns={@ORM\JoinColumn(name="item_id", >>> referencedColumnName="id")}, >>> * inverseJoinColumns={@ORM\JoinColumn(name="item_category_id", >>> referencedColumnName="id")} >>> * ) >>> */ >>> protected $categories; >>> >>> public function __construct() >>> { >>> $this->categories = new ArrayCollection(); >>> } >>> } >>> >>> >>> In my *CategoryRepository, *I am trying to select just one category >>> which has more than 1 course in it, and all of it's courses, in the same >>> query. >>> >>> /** >>> * Get a category that has multiple (>1) courses in it. >>> * >>> * @return \models\Category|null >>> */ >>> public function getOneWithCourses() >>> { >>> $qb = $this->_em->createQueryBuilder(); >>> $q = $qb->select('category') >>> ->from('models\Category', 'category') >>> ->innerJoin('category.courses', 'courses') >>> ->having('COUNT(courses.id) > 1'); >>> return $q->getQuery()->*setMaxResults(1)*->getOneOrNullResult(); >>> } >>> >>> If I do: $q = $qb->select('category', *'courses'*), then I get one >>> category and only ONE of the courses. It only gets all of the courses if I >>> do not select the courses, and just rely on lazy-loading. >>> >>> Is there a way to do this in one query, or is this really the right way >>> to do it? >>> >> -- >> You received this message because you are subscribed to the Google Groups >> "doctrine-user" group. >> To unsubscribe from this group and stop receiving emails from it, send an >> email to [email protected] <javascript:>. >> To post to this group, send email to [email protected] >> <javascript:>. >> Visit this group at http://groups.google.com/group/doctrine-user. >> For more options, visit https://groups.google.com/d/optout. >> > >
-- You received this message because you are subscribed to the Google Groups "doctrine-user" group. To unsubscribe from this group and stop receiving emails from it, send an email to [email protected]. To post to this group, send email to [email protected]. Visit this group at http://groups.google.com/group/doctrine-user. For more options, visit https://groups.google.com/d/optout.
