Hi Jessica,
Filtering/grouping on fetch-joined results is going to break hydration.
To fix that, you should be able to do do a second join:
SELECT
category, courses
FROM
models\Category category
JOIN
category.courses courses
JOIN
category.courses courses_count
GROUP BY
category.id, courses.id
HAVING
COUNT(courses_count.id) > 1
Marco Pivetta
http://twitter.com/Ocramius
http://ocramius.github.com/
On 28 May 2014 02:18, Jessica Mauerhan <[email protected]> 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].
> 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.
>
--
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.
