Query one entity according to another joined entity field values

Question

I have this OneToMany relationship between two entities: Sculpture(1) and Image(n). My goal is to query all Sculptures that have all their Image.featured set to 0. If a Sculpture have at least one Image that has featured = 1, it shouldn't be retrieved by the query (by design, only one image by sculpture can be featured anyway).

Here are the tables generated:

CREATE TABLE IF NOT EXISTS `image` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `sculpture_id` int(11) DEFAULT NULL,
  `nom` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `featured` tinyint(1) NOT NULL,
  `type` enum('mini','normal') COLLATE utf8_unicode_ci DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `IDX_C53D045FB2720858` (`sculpture_id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

and

CREATE TABLE IF NOT EXISTS `sculpture` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `titre` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `reference` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `largeur` int(11) NOT NULL,
  `hauteur` int(11) NOT NULL,
  `annee` varchar(4) COLLATE utf8_unicode_ci NOT NULL,
  `matiere` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `active` tinyint(1) NOT NULL,
  `creation` datetime NOT NULL,
  `description` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `hits` int(11) NOT NULL,
  `taille` enum('xs','s','m','l','xl') COLLATE utf8_unicode_ci DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

with

ALTER TABLE `image`
  ADD CONSTRAINT `FK_C53D045FB2720858` FOREIGN KEY (`sculpture_id`) REFERENCES `sculpture` (`id`);

I tried querying the Sculpture entity with this Repository method:

class SculptureRepository extends EntityRepository
{
    public function findByFeatured($featured)
    {
        $query = $this->createQueryBuilder('s')
            ->leftJoin('AppBundle\Entity\Image', 'i', 'WITH', 'i.sculpture = s.id')
            ->where('i.featured = :featured')
            ->setParameter('featured', $featured)
            ->orderBy('s.id', 'DESC')
            ->groupBy('s')
            ->getQuery()
        ;

        return $query->getResult();
    }
}

and querying the Image entity with this Repository method:

class ImageRepository extends EntityRepository
{
    public function findNoFeatured()
    {
        $query = $this->createQueryBuilder('i')
            ->where('i.featured = 0')
            ->groupBy('i.sculpture')
            ->getQuery();

        return $query->getResult();

    }
}

But these return all Sculptures when I want only the ones that have no featured Image.

Any ideas?

Thanks!

I think it would help (at least me, personally) if you could have included the query which would give you the desired results. — Tim Biegeleisen
@TimBiegeleisen: You mean the raw SQL query? Haven't figured it out yet unfortunately. — D4V1D
Well it seems to me you better sort that out first, before you try using a framework which sits on top of your database. — Tim Biegeleisen
@TimBiegeleisen: Yes, I'm working on it but thought of asking on SO in the meanwhile. — D4V1D
Nothing wrong with asking the question. I just voted you up to help :-) — Tim Biegeleisen

Richard Richard · Accepted Answer · 2015-08-19T05:23:06

Something like this:

$query = $this->createQueryBuilder('s, count(i.id) as featured_image_count')
    ->leftJoin('AppBundle\Entity\Image', 'i', 'WITH', 'i.sculpture = s.id')
    ->where('i.featured = :featured')
    ->setParameter('featured', 1)
    ->orderBy('s.id', 'DESC')
    ->groupBy('s')
    ->having('featured_image_count < 1')
    ->getQuery()
;

Alternatively you could use a subquery to fetch all images where featured == 1, then you could use a not in to eliminate all those sculptures, like:

$qb = $this->createQueryBuilder();
$qb2 = $qb;
$qb2->select('i.sculptureId')->distinct(true)
->from('AppBundle\Entity\Image', 'i')
->where('i.featured = 1');

$qb = $this->createQueryBuilder();
$qb->select('s')
->from('AppBundle\Entity\Sculpture', 's')
->where($qb->expr()->notIn('s.id', $qb2->getDQL())
);

$result = $qb->getQuery()->getResult();

I haven't syntax checked any of this, but both approaches will work fine.

Query one entity according to another joined entity field values

1 Answers