How do I query results by a nested table property?

Question

I have a table lead_check that is joined to a table prospect which is joined to a table account. How can I query results such that account.is_prospect = 0?

So far I am checking lead_check and calling .joinRelated('prospect') which works, but then chaining on .joinRelated('account') gives me the error A model class LeadCheck doesn't have relation account so I'm unable to check the value of account.is_prospect

This is the query so far:

        const query = TridentLeadCheck.query()
            .alias('leadCheck')
            .select(
                'prospect.id',
                'leadCheck.success',
                'leadCheck.prospectId',
                'leadCheck.addedById as addedBy',
                'leadCheck.createdAt',
                'leadCheck.updatedAt',
            )
            .joinRelated('prospect')
            .orderBy(field, sort)
            .limit(limit)
            .offset(offset)
            .where({ success: 1 })
            .where({ 'prospect.status': 'pending' });

This is how I have attempted to get the value of is_pending:

            query.joinRelated('account').where({ is_prospect: 0 });

Answer 1

Just worked it out, posting the answer if anyone else has the same problem. You can join the nested account with square brackets:

query.joinRelated('prospect.[account]').where({ is_prospect: 1 });