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

I found out that $query->contains() only works properly with plain _mm tables.

So this is what I did: I just added a view to the DB which has the required fields for a _mm table:

CREATE VIEW `company_type_mm` AS
SELECT 
    `company` AS `uid_local`, 
    `type` AS `uid_foreign`,
    0 AS `sorting`,
    0 AS `sorting_foreign` 
FROM `dept`;

Then I added a new field dept to the TCA of the company table:

'type' => array(
    ...
    'config' => array(
        'foreign_table' => 'type',
        'MM' => 'company_type_mm',
        ...
    )
)

And now I get the right results for companies which have departments of type A and type B like this:

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