Find related entries by specific position in the entries field

Question

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 aodihis' 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 aodihis 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',
    ])
%}

Answer 1

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() %}