0

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',
    ])
%}
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() %}
4
  • 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
    Sep 3 at 9:51
  • @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
    Sep 3 at 11:23
  • Also I put some alternative ways to approach this but not sure this is the good ways.
    – aodihis
    Sep 3 at 11:31
  • 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
    Sep 3 at 12:22

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.