<p>I don't think there is an option in current craft to get the relations query based on the order.
But you can do that, using where statements like this :</p>
<pre><code>{% set offers_for_institute = craft.entries().section('offers')
.innerJoin('relations', 'elements.id = relations.sourceId')
.andwhere('relations.sortOrder = 1')
.andWhere(&quot;relations.targetId = #{entry.id}&quot;)
.andWhere(&quot;relations.fieldId = #{field.id}&quot;)
.all() %}
</code></pre>
<p>But these queries will give some duplicates because the craft will do join both inside subquery and on the main query but the where statement will only applied on subquery. The other alternative can be like this, but not sure this is a good ways</p>
<pre><code>{% set offers_for_institute = craft.entries().section('offers')
.innerJoin('relations', ['and','elements.id = relations.sourceId', 'relations.sortOrder = 1', &quot;relations.targetId = #{entry.id}&quot;, &quot;relations.fieldId = #{field.id}&quot;])
.all() %}
</code></pre>

CC BY-SA 4.0
2021-09-03T09:51:22.210Z
Thanks for the reply! I've tried to use something like this, but couldn't get it to work  properly. I've updated my question with a code example based on yours, but using placeholder syntax to accomodate for environments with database prefixes. The problem is that now I'm getting loads of duplicate rows (multiple rows for each related offer), any idea why? Also, this might seem stupid, but why specify the concrete entry id and field id in the join? Shouldn't the join just specify which columns to join on, instead using a where clause to select specific rows by ID?
MoritzLost
CC BY-SA 4.0
2021-09-03T11:23:37.917Z
@MoritzLost, sorry my mistake for did a wrong joining table, I made an update with the query, but still giving duplicate results because the craft put the innerJoin command inside both the subquery and the main query but didn't do the same for the where statements. If you want to check you can change the all() to getRawSql().
aodihis
CC BY-SA 4.0
2021-09-03T11:31:51.903Z
Also I put some alternative ways to approach this but not sure this is the good ways.
aodihis
CC BY-SA 4.0
2021-09-03T12:22:12.613Z
Thanks a lot! The first approach still gave me duplicates, but the second one works! I've updated my answer with the final code including the adjustments for database prefixes, now it's working as intended. Good catch regarding the subqueries. No idea how anyone is supposed to extend element queries given gotchas like this ^^' Feels a bit weird to put all conditions in the join, but as long as it works … Anyway, I guess Craft's element queries aren't really meant for stuff like this. Thanks for your help!
MoritzLost
CC BY-SA 4.0
2021-09-03T09:13:49.677Z
2021-09-03T13:54:13.907Z
2021-09-03T13:54:13.907Z
aodihis
<p>I'm looking for a way to limit a reverse relations query to only match the <em>first selected entry</em> in an entries field.</p>
<p>Some context: I have two entry types, <code>institutes</code> and <code>offers</code>. Offers have an <code>institutes</code> field to relate that offer to one or more institutes. Now given an institute, I want to find all offers where this institute is the first selected institute. So far I only got this:</p>
<pre><code>{# The `entry` here is an `institute`. #}

{% set offers_for_institute = craft.entries().section('offers')
    .relatedTo({ targetElement: entry, field: 'institutes', })
    .all() %}
</code></pre>
<p>But of course, this finds all offers where the institute is selected, regardless of position. One option would be to filter the results manually, but I would like to do this in one query. Is this possible using element queries? Maybe with a custom select statement?</p>
<hr />
<p><strong>Edit based on aodihis' answer:</strong> For anyone looking for something similiar, here's the final working code. The key was putting all conditions in the join to avoid duplicates because of the way Craft puts added joins and conditions in the main and sub query respectively. See the comments below aodihis answer for details.</p>
<pre><code>{% set field = craft.app.fields.getFieldByHandle('institutes') %}
{% set offers_for_institute = craft.entries().section('offer')
    .innerJoin('{{%relations}} as relations', [
        'and',
        '[[relations.sourceId]] = elements.id',
        &quot;[[relations.targetId]] = #{entry.id}&quot;,
        &quot;[[relations.fieldId]] = #{field.id}&quot;,
        '[[relations.sortOrder]] = 1',
    ])
%}
</code></pre>

<entries><relations><query><elementquery>
Find related entries by specific position in the entries field
# Find related entries by specific position in the entries field

(Asked by: username_0 on 2021-08-23)

I'm looking for a way to limit a reverse relations query to only match the first selected entry in an entries field.
Some context: I have two entry types, institutes and offers. Offers have an institutes field to relate that offer to one or more institutes. Now given an institute, I want to find all offers where this institute is the first selected institute. So far I only got this:
{# The `entry` here is an `institute`. #}

{% set offers_for_institute = craft.entries().section('offers')
    .relatedTo({ targetElement: entry, field: 'institutes', })
    .all() %}

But of course, this finds all offers where the institute is selected, regardless of position. One option would be to filter the results manually, but I would like to do this in one query. Is this possible using element queries? Maybe with a custom select statement?

Edit based on username_1' answer: For anyone looking for something similiar, here's the final working code. The key was putting all conditions in the join to avoid duplicates because of the way Craft puts added joins and conditions in the main and sub query respectively. See the comments below username_1 answer for details.
{% set field = craft.app.fields.getFieldByHandle('institutes') %}
{% set offers_for_institute = craft.entries().section('offer')
    .innerJoin('{{%relations}} as relations', [
        'and',
        '[[relations.sourceId]] = elements.id',
        "[[relations.targetId]] = #{entry.id}",
        "[[relations.fieldId]] = #{field.id}",
        '[[relations.sortOrder]] = 1',
    ])
%}

**Tags:** entries, relations, query, elementquery


---

## 1 answers

### Answer by username_1 on 2021-09-03. Score: 1 | Accepted answer

I don't think there is an option in current craft to get the relations query based on the order.
But you can do that, using where statements like this :
{% set offers_for_institute = craft.entries().section('offers')
.innerJoin('relations', 'elements.id = relations.sourceId')
.andwhere('relations.sortOrder = 1')
.andWhere("relations.targetId = #{entry.id}")
.andWhere("relations.fieldId = #{field.id}")
.all() %}

But these queries will give some duplicates because the craft will do join both inside subquery and on the main query but the where statement will only applied on subquery. The other alternative can be like this, but not sure this is a good ways
{% set offers_for_institute = craft.entries().section('offers')
.innerJoin('relations', ['and','elements.id = relations.sourceId', 'relations.sortOrder = 1', "relations.targetId = #{entry.id}", "relations.fieldId = #{field.id}"])
.all() %}

> **Comments:**
> **username_0**: Thanks for the reply! I've tried to use something like this, but couldn't get it to work  properly. I've updated my question with a code example based on yours, but using placeholder syntax to accomodate for environments with database prefixes. The problem is that now I'm getting loads of duplicate rows (multiple rows for each related offer), any idea why? Also, this might seem stupid, but why specify the concrete entry id and field id in the join? Shouldn't the join just specify which columns to join on, instead using a where clause to select specific rows by ID?  
> **username_1**: @username_0, sorry my mistake for did a wrong joining table, I made an update with the query, but still giving duplicate results because the craft put the innerJoin command inside both the subquery and the main query but didn't do the same for the where statements. If you want to check you can change the all() to getRawSql().  
> **username_1**: Also I put some alternative ways to approach this but not sure this is the good ways.  
> **username_0**: Thanks a lot! The first approach still gave me duplicates, but the second one works! I've updated my answer with the final code including the adjustments for database prefixes, now it's working as intended. Good catch regarding the subqueries. No idea how anyone is supposed to extend element queries given gotchas like this ^^' Feels a bit weird to put all conditions in the join, but as long as it works … Anyway, I guess Craft's element queries aren't really meant for stuff like this. Thanks for your help!  

---

