Joining sections (Channel) to query based on a related item attribute

Question

I've been trying this for a while now, so might have read this before. Sorry about that, I'm struggling really hard to get this right. At first I tried (wrongly) with the eager load features.

I have two channels: events and shows. In the events you can select a show with an entry field. The shows items have a category attached. I need to filter the events, based on the category of the show

Like events with shows where show has category 1. They told me on the slack channel to join the tables I needed, but cannot manage to do it.

Answer 1

Not sure about doing this in a single query. Referencing this doc page section, https://docs.craftcms.com/v3/relations.html#templating, you can make use of relatedTo() by passing in IDs.

Assuming you know the ID of the category already and that ID is 1:

{% set showIds = craft.entries.section('shows').relatedTo(1).ids() %}

{% set entries = craft.entries.section('events').relatedTo(showIds).all() %}

The relatedTo() method doesn't care if the IDs are from Entries or Categories, or even Users and Tags. They're all Elements stored in the elements table so they have unique IDs.