Doctrine 2 - Unwanted Loading Doesn't Work with ManyToMany
I have three objects: SequenceRun
has one MaterialTypeString
and has many User
. That is, there is a OneToMany relationship between SequenceRun
and MaterialTypeString
and there is a ManyToMany relationship between SequenceRun
and User
.
SequenceRun.php:
/**
* @ORM\ManyToOne(targetEntity="MaterialTypeStrings", inversedBy="sequenceRuns")
* @ORM\JoinColumn(name="material_type_strings_id", referencedColumnName="id")
*/
private $materialTypeString;
/**
* Many Groups have Many Users.
* @ORM\ManyToMany(targetEntity="FOSUser", mappedBy="sequenceRuns")
*/
private $users;
Now, without eager loading, my index method:
/**
* @Route("/sequence_run/index", name="sequence_run_index")
*/
public function indexAction() {
// Grab all experiments from database and hand them to template.
$repository = $this->getDoctrine()->getRepository('AppBundle:SequenceRun');
$sequence_runs = $repository->findAll();
return $this->render('sequence_run/index.html.twig',['sequence_runs' => $sequence_runs]);
}
Makes 4 requests for one object SequenceRun
:
SELECT t0.username AS username_1, t0.username_canonical AS username_canonical_2, t0.email AS email_3, t0.email_canonical AS email_canonical_4, t0.enabled AS enabled_5, t0.salt AS salt_6, t0.last_login AS last_login_7, t0.confirmation_token AS confirmation_token_8, t0.password_requested_at AS password_requested_at_9, t0.roles AS roles_10, t0.id AS id_11, t0.dn AS dn_12, t0.cn AS cn_13, t0.department AS department_14, t0.department_dn AS department_dn_15, t0.from_bio_control AS from_bio_control_16, t0.password AS password_17 FROM fos_user t0 WHERE t0.id = ? LIMIT 1
Parameters: [0 => 96]
SELECT t0.id AS id_1, t0.start_date AS start_date_2, t0.end_dat AS end_dat_3, t0.kit AS kit_4, t0.run_coverage_target AS run_coverage_target_5, t0.read_length AS read_length_6, t0.created_at AS created_at_7, t0.updated_at AS updated_at_8, t0.material_type_strings_id AS material_type_strings_id_9 FROM sequence_run t0
Parameters: []
SELECT t0.username AS username_1, t0.username_canonical AS username_canonical_2, t0.email AS email_3, t0.email_canonical AS email_canonical_4, t0.enabled AS enabled_5, t0.salt AS salt_6, t0.last_login AS last_login_7, t0.confirmation_token AS confirmation_token_8, t0.password_requested_at AS password_requested_at_9, t0.roles AS roles_10, t0.id AS id_11, t0.dn AS dn_12, t0.cn AS cn_13, t0.department AS department_14, t0.department_dn AS department_dn_15, t0.from_bio_control AS from_bio_control_16, t0.password AS password_17 FROM fos_user t0 INNER JOIN users_sequence_runs ON t0.id = users_sequence_runs.fosuser_id WHERE users_sequence_runs.sequence_run_id = ?
Parameters: [0 => 2]
SELECT t0.id AS id_1, t0.type AS type_2 FROM material_type_strings t0 WHERE t0.id = ?
Parameters: [0 => 5]
If I add fetch = "EAGER"
in MaterialTypeString
, it drops the last request (as expected). But if I add also add it to User
, I get 3 requests.
Does loading eager
do ManyToMany relationships, or will I have to use DQL
and write the query manually? (If so, what will it look like?)
source to share
What are you trying to achieve? If you want to have one query versus your db where you fetch all the entities you need, you have to write your own query by hand, it is not a matter of impatient or lazy loading.
You could write something like this
$sequence_runs=$repository->createQueryBuilder('sr')->addSelect(['mts', 'fu'])
->leftJoin('sr.materialTypeString', 'mts')
->leftJoin('sr.users', 'fu')
->getQuery()->getResult();
You use eager loading when you want the doctrine to do whatever it needs to, whether you use your associations in your code or not, while you use lazy loading so that your associations are fully hydrated only if you access them in your code, but the number of requests will be the same if you access all your associations in your code.
Is it because you are showing a paginated list of entities? Then try writing your own query using DQL or Query Builder to load all the required entities according to your views. Are you showing a whole? Then fetch it from the repository by lazy loading your associations and let doctrine do the job!
source to share