[5.x]: Address queries return addresses that belong to disabled entries by default

[MoritzLost]

What happened?

Description

I just found out the hard way that address queries don't exclude addresses that belong to entries that are disabled, expired or archived. This entry status is not taken into account at all as far as I can tell. This will return addresses for entries that are disabled:

$addresses = Address::find()
    ->field('myAddressField')
    ->all();

Steps to reproduce

See above.

Expected behavior

I encountered this in the context of addresses, but really it applies to all nested elements.

By default, queries for nested elements (like addresses) should only return results where the owner would be returned by a regular query as well. An entry query doesn't return disabled entries by default. So an address query shouldn't return addresses that belong to a disabled entry by default, either.

The entry status (as well as the expiry date) are built-in features by Craft that are supposed to work by default. If I want to get all published entries, I can just use a normal entry query and Craft will add those conditions automatically. It's counterintuitive that this 'contract' is not honored when querying nested elements.

In addition, the NestedElementQueryTrait should provide an easy method for adding this condition. Similar to allowOwnerDrafts(), allowOwnerRevisions(), I'm imagining something like ownerStatus() that limits the query as described above. Or rather, this should be the default, and this method above should allow us to loosen that condition.

Actual behavior

There's no simple way to exclude addresses for disabled, expired or archived entries. The only way I found was using custom joins and conditions:

$addresses = Address::find()
    ->field('myAddressField')
    ->innerJoin(
        '{{%elements}} owner_elements',
        '[[addresses.primaryOwnerId]] = [[owner_elements.id]]'
    )
    ->innerJoin(
        '{{%entries}} owner_entries',
        '[[owner_elements.id]] = [[owner_entries.id]]'
    )
    ->andWhere(['owner_entries.status' => 'live'])
    ->all();

I'm not even sure that this works fully as intended. Also, it only works if staticStatuses is used, otherwise I'd have to add conditions for all the columns that determine the entry status manually.

Craft CMS version

5.8.18

PHP version

No response

Operating system and version

No response

Database type and version

No response

Image driver and version

No response

Installed plugins and versions

[brandonkelly]

Converted this to a discussion since it’s working as intended; element statuses don’t impact nested element statuses.

A new ownerStatus param might mostly help with addresses, but not so much for entries, which are more likely to be nested multiple levels down. Ideally you’d be able to exclude nested elements whose owners – all the way up to the root owner – are all enabled, not just the direct owner.

There’s no efficient way to do that right now though. We’d need to start tracking elements’ inherited statuses in the DB – maybe with a new enabledByOwner column on the elements table.

[MoritzLost]

@brandonkelly Fair enough. Yes, a query parameter to easily limit the query by root owner status would be very useful.

There’s no efficient way to do that right now though. We’d need to start tracking elements’ inherited statuses in the DB – maybe with a new enabledByOwner column on the elements table.

My solution with the two joins seems to work as expected, or am I missing something there? But yeah, this only works for one nesting level. That would already be a handy option to have, though.

[brandonkelly]

I’d rather come up with something that works with multiple levels. Spent some time on it yesterday, but it’s still a WIP: #18152

[MoritzLost]

@brandonkelly That looks very promising!