Doctrine query get only the "first" of many related (doing join & select to avoid N+1)

Question

In this situation I have 3 related entities profile --OneToMany--> annualReports --ManyToOne--> photo

And in a loop, for a table I am checking for the existence of the photo in twig like this

{% set is_photo = profile.annualReports.toArray()[0].photo.id is defined %}

So, a profile can have a lot of annualReports - this paticular situation I want the very first "report" only.

The question is how do I do this in the QueryBuilder? That is join the "first Report" ("first" can be based on minimum id or createdAt date)

UPDATE: I guess the question is how can I 'LIMIT 1' and specify a sort?

Your question is unclear to me. But for your last part, sorting is done by using `->orderBy()`. To have better twig, use `profile.annualReports.first` — goto, Feb 25 '19 at 16:42
sorry, the orderBy needs to be applied to the joining table. it's 1 object, profile, and joined to it is the "first" of many reports. — Chad, Feb 25 '19 at 17:03
like this https://stackoverflow.com/questions/8161438/select-first-record-in-a-one-to-many-relation-using-left-join but in doctrine/symfony — Chad, Feb 25 '19 at 18:12
also you can add a condition in previus foreach instead of define a variable, check https://twig.symfony.com/doc/2.x/tags/for.html#adding-a-condition — Manuel Garcia, Feb 26 '19 at 08:28

score 1 · Answer 1 · answered Feb 25 '19 at 22:15

If you want to get the first report in twig, you simply need to use the method first of the Collection class :

{% if profile.annualReports IS NOT EMPTY %}
   {% set report = profile.annualReports.first %}
{% endif%}


/**
 * @ManyToMany(targetEntity="Group")
 * @OrderBy({"name" = "ASC"})
 */
private $groups;

1 Answers1