I use QueryBuilder to get the 10 largest cities with fortunes.
$query = $em->createQueryBuilder()
->select('c','s')
->from('CitiesBundle:Cities', 'c')
->innerJoin('c.state', 's','WITH', 'c.state = s')
->orderBy('c.population', 'DESC')
->setMaxResults(10)
->getQuery();
generated SQL:
SELECT c0_.id AS id0, c0_.name AS name1, c0_.landarea AS landarea2, c0_.density AS density3, c0_.population AS population4, s1_.id AS id5, s1_.name AS name6, c0_.state_id AS state_id7 from cities c0_ INNER JOIN s1_ ON c0_.state_id = s1_.id AND (c0_.state_id = s1_.id) ORDER BY c0_. population DESC LIMIT 10
When I test this request in PHPMyAdmin, each city has its own state, but in the application all the cities in the array have a connection with the state of the first city.
Can someone explain to me the behavior of Doctrine2 in this case?
[EDIT]
Scheme:
XYZ\Bundle\CitiesBundle\Entity\Cities:
type: entity
table: cities
fields:
oneToMany:
state:
targetEntity: States
cascade: { }
mappedBy: null
inversedBy: null
joinColumns:
state_id:
referencedColumnName: id
orphanRemoval: false
lifecycleCallbacks: { }
XYZ\Bundle\CitiesBundle\Entity\States:
type: entity
table: states
fields:
id:
id: true
type: boolean
nullable: false
generator:
strategy: IDENTITY
name:
type: string
length: 50
fixed: false
nullable: false
lifecycleCallbacks: { }
I am using a different scheme (ManyToOne etc.) but no luck
PHPMyAdmin, mazowieckie .. - . 10 state- > name mazowieckie.

:

: {{city.state.name}}