TYPO3 Extbase Repository Query: How to find records in M:N relation where several values for N are given?

Question

We have a simple model Company. Each company can have one ore more departments Dept. Each department is of a certain type Type.

Now we need a query where all companies are returned, which have a department of type X and one of type Y at least (i.e. each returned company has two or more departments, at least one X and one Y).

How can that be done with a query?

This query gives no results if getTypes returns more than one type.

if (count($types = $demand->getTypes()) > 0) {
    foreach ($types as $type)
        $constraints[] = $query->contains('dept.type', $type);
}

$result = $query->matching($query->logicalAnd($query->logicalAnd($constraints)))->execute();

This query returns results for type X or Y

if (count($types = $demand->getTypes()) > 0) {
    $constraints[] = $query->in('dept.type', $types);
}

The tables look like this (simplified):

CREATE TABLE IF NOT EXISTS `company` (
  `uid` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(128) NOT NULL,
  PRIMARY KEY (`uid`)
);

CREATE TABLE IF NOT EXISTS `dept` (
  `uid` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(128) NOT NULL,
  `company` int(10) unsigned NOT NULL,
  `type` int(10) unsigned NOT NULL,
  PRIMARY KEY (`uid`)
);

CREATE TABLE IF NOT EXISTS `type` (
  `uid` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(128) NOT NULL,
  PRIMARY KEY (`uid`)
);

Answer 1

if (count($types = $demand->getTypes()) > 0) {
    foreach ($types as $type)
        $constraints[] = $query->contains('dept.type', $type);
}

You do not show the further processing.

If you need AND operation use this:

$result = $query->matching($query->logicalAnd($query->logicalAnd($constraints)))->execute();

If you need OR operation use this:

$result = $query->matching($query->logicalAnd($query->logicalOr($constraints)))->execute();

HTH