How to return a list of entries that are related to a specific element in a Hyper field

[croxton]

My use case is a 'Used by' tab on Assets, using a custom template in the field layout. I'd like to be able to list all entries that are related to the asset via a Hyper field element relationship. This is straightforward to do for native element relationships, but I'm having a hard time working out how to query reverse relationships to Hyper fields.

{% set rels = craft.entries.relatedTo(element).siteId('*').collect() %}
{% set owners = [] %}

{% for rel in rels %}
    {% set owner = rel.owner ?? rel %}
    {% set owner = owner.primaryOwner ?? owner %}
    {% if owner.canonicalId not in owners %}
        {% set owners = owners + {
            (""~owner.canonicalId~"") : {
                cpEditUrl : owner.cpEditUrl,
                title : owner.title,
                site: owner.site.name,
                section: owner.section.name,
                canSave: owner.canSave(currentUser)
            }
        } %}
    {% endif %}
{% endfor %}

<div class="pane">
    {% if owners|length %}
        <h2>This content is used by the following entr{{ owners|length == 1 ? 'y' : 'ies' }}:</h2>
        <ul style="list-style: square; margin-left: 1rem;">
            {% for item in owners|sort((a, b) => a.title <=> b.title) %}
                <li>
                    {% if item.canSave %}
                        <a href="{{ item.cpEditUrl }}">
                            {{ item.title }}
                        </a>
                    {% else %}
                        {{ item.title }}
                    {% endif %}
                    ({{ item.site }} ▸ {{ item.section }})
                </li>
            {% endfor %}
        </ul>
    {% else %}
        <h2>This content appears to be unused by any entries at the moment</h2>
    {% endif %}

</div>

[engram-design]

Yeah, this'll be almost impossible to do, because Hyper doesn't store relationships in either its own database tables, or make use of the Craft relations database table. Because Hyper links store all their content as JSON, you'd have to dive into the JSON content, or do a JSON query across Hyper content, which is performantly expensive.

We're absolutely looking into options to improve this, likely adding a relations table to handle this sort of thing.

[croxton]

I guess you could make a generated column to extract the element IDs from the JSON, and add an index to it that can be queried?
https://planetscale.com/blog/indexing-json-in-mysql

Good to know you are looking into options, this would be a nice thing to have.

[engram-design]

Indeed, one of the options we're looking at!