Matrix queries with `:notempty:` for fields that exist in multiple blocks produce no results

[MoritzLost]

Description

I'm trying to find all matrix blocks on a page where a particular field is not empty. The field exists on some of the blocks, but not all of them, and has the same handle for every block. Of course, even though the fields have the same handle, they get separate columns in the database. Here's my selector for this query (sections is the matrix field, jumplink is the text field that exists on multiple blocks):

$sectionsWithAnchors = (clone $entry->sections)
    ->type(['section', 'cta'])
    ->jumplink(':notempty:')
    ->all();

In this case, I want all blocks where the jumplink field for that block is not empty. Unfortunately, this query always produces an empty result, because the jumplink(':notempty:') call creates a WHERE close that's joined with AND, so it can only find blocks where both of the fields are not empty. This isn't possible of course, so the query can never find anything.

Is this the intended behaviour? More generally, is having fields with the same name in different blocks something to avoid?

In this my case, joining the WHERE clauses with OR instead of AND would solve this issue, though I'm not sure it would produce the expected results for all queries. If this isn't a viable fix, how could I work around this limitation? Of course, retrieving all blocks and then filtering is an options, though I'd like to avoid that for performance reasons. I tried to use a custom andWhere condition, but couldn't find a simple way to get the database column names for matrix fields without hardcoding them. Is there a better way?

Steps to reproduce

1. Create a matrix field with at least two block types (e.g. section and cta).
2. In both block types, add a text field with the same name (e.g. jumplink).
3. Create an entry with that field and add some blocks of both types, filling out the field in question.
4. Use the query above to try to find all blocks where that field is filled out. The query will return an empty result.

Additional info

• Craft version: 3.7.30.1
• PHP version: 8.1
• Database driver & version: MySQL 8.0

[timkelty]

@MoritzLost this is likely a bug, but doesn't make sense to address until element querying receives major refactoring (Craft 5).

In the meantime, as you alluded to – I would suggest avoiding name conflicts between block types like this.

I'll keep this issue open and tag for v5.