• 6
name

A PHP Error was encountered

Severity: Notice

Message: Undefined index: userid

Filename: views/question.php

Line Number: 191

Backtrace:

File: /home/prodcxja/public_html/questions/application/views/question.php
Line: 191
Function: _error_handler

File: /home/prodcxja/public_html/questions/application/controllers/Questions.php
Line: 433
Function: view

File: /home/prodcxja/public_html/questions/index.php
Line: 315
Function: require_once

name Punditsdkoslkdosdkoskdo

How can I limit an associated entity result in Doctrine2?

I have the following query:

$query = $this->getEntityManager()->createQuery('
                      SELECT u, p, m
                      FROM MyCoreBundle:User u
                      JOIN u.programmes p
                      JOIN u.motivation m
                      ');

$result = $query->getResult();

I want to restrict the motivation objects returned for each user to be the result of this second query which I am using elsewhere (On the Motivation repository):

$query = $this->getEntityManager()->createQuery('
                      SELECT m FROM MyCoreBundle:Motivation m
                      WHERE m.user = :user
                      ORDER BY m.date DESC');

$query->setParameter('user',$user);
$query->setFirstResult(0);
$query->setMaxResults(1);
//@TODO if there is not result recorded for the user, return sth which indicates this
return $query->getResult();

Is there a way to limit and restrict motivation in the first query or a better approach?

    • Considering written below, and assuming that "latest motiviation" is of primary importance for you and it will be accessed a lot, possible solution is to create User:LatestMotivation OneToOne relatinship. And then every time a new Motivation entity is added, (via Doctrine prePersist events) update the LatestMotivation with the newly added one. This way you will be able to iterate over many User records fetching the latest motivation. HTH

You can't limit the number of joint rows.

If you have Doctrine 2.1 you can use ->slice() on the collection:

$collection = $user->getMotivations();    // returns a LazyCollection, 
                                          // makes no SQL query

$motivations = $collection->slice(0, 20); // queries the first 20 motivations 
                                          // for this user (if the association
                                          // was not fetch-joint)

See http://www.doctrine-project.org/docs/orm/2.0/en/tutorials/extra-lazy-associations.html

  • 16
Reply Report
      • 1
    • Thanks - I got round it by adding another method to my User Entity which just adds a single motivation entry, looping over the object and overwriting the $motivation property. Dirty but it works.
    • @amaud576875 Unfotunately the slice method initializes the associations only for the current user. If you loop over multiple users multiple queries are produced. Is there a way how to fetch sliced collection for all the users?
      • 1
    • @arnaud576875 slice() method seems cool, but there're two pitfalls: 1) the association User.motivations should be declared as fetch="EXTRA_LAZY". If you leave it to default value of "LAZY" then ALL of the motivations will be queried from DB and then sliced, instead of only querying 20. 2) queries the first 20 motivations is a bit of vague... usually for solving your domain task you may want latest 20 motivations e.g. ORDER BY m.date DESC but slice() doesnt provide chance to sort them. Thus despite I was very excited to find slice() it quickly unfit for solving real problem

Warm tip !!!

This article is reproduced from Stack Exchange / Stack Overflow, please click

Trending Tags

Related Questions