Symfony Doctrine SQL - SELECT WHERE 2 rows from related table have two values (SQL WITH)

Hi,

in Symfony with SQL Doctrine I have table1 which has a related JOIN via join_id on table2:

tabulka1
------------------------------------------
id name
---------------------
1 karel
2 petr

tabulka 2
------------------------------------------
value join_id
---------------------
green 1
purple 1
blue 2

And I want to SQL select all records from table1 that have a value of green and purple in table 2 (really both). Can you please advise how to do it? Thanks

REPLY

Hi,

in Symfony with Doctrine SQL, you could execute such a query as follows:

public function findAllByMulti()
{
return $this->createQueryBuilder('s')
->join('u.table_2', 'color1', 'WITH', 'color1.value = :var1')
->join('u.table_2', 'color2', 'WITH', 'color2.value = :var2')
->setParameters([
'var1' => 'green',
'var2' => 'purple'
])
->getQuery()
->execute();
}