ElementQuery fails if both join and search are set

[sebastian-lenz]

Description

When combining both join and search options in an element query a database exception will be thrown.

Steps to reproduce

1. Run the following element query:

Entry::findAll([
  'search' => 'Some query string',
  'join' => [
    ['LEFT JOIN', Table::CONTENT . ' myContent', 'myContent.elementId = elements.id']
  ],
  'where' => 'myContent.dateCreated > NOW()'
]);

2. An exception will ne thrown: SQLSTATE[42S22]: Column not found: 1054 Unknown column 'myContent.dateCreated' in 'where clause'

Background

The class ElementQuery will apply the search parameter in the method prepare before the join parameters are applied. The method _applySearchParam will execute the current query in its intermediate, unfinished state causing the exception.

Additional info

• Craft version: Craft Pro 3.2.10
• PHP version: 7.3.8
• Database driver & version: MySQL 5.7.27

[sebastian-lenz]

I am not really sure why this is a minor bug. The code that causes the error only uses methods described in the official Craft documentation:

https://docs.craftcms.com/v3/dev/element-queries/#executing-element-queries

(Also our customer does not think it's a minor issue 😉)

I've created a pull request that solves that issue for us, but as the element query is such an important service in Craft it probably has to be triple-checked.

[brandonkelly]

It was minor in that it’s not a bug you would run into under normal circumstances; most people are not ever overriding the JOIN or WHERE clauses. Even in your case the custom JOIN wasn’t necessary, as the content table is already joined into entry queries. So you could just do this:

Entry::find()
    ->search('Some query string')
    ->andWhere('[[content.dateCreated]] < NOW()')
    ->all();

Still, it’s been fixed for the next release. To get the fix early, change your craftcms/cms requirement in composer.json to:

"require": {
  "craftcms/cms": "dev-develop#82e14b2c1be3c9afeac9dbfb751f63da707153b9 as 3.2.10",
  "...": "..."
}

Then run composer update.

[sebastian-lenz]

Thank you very much for looking into it! Obviously the example code did not make much sense, in our real code a custom table is joined. I wanted to simplify the code so it shows the error without any prerequisites.

[brandonkelly]

Ah ok gotcha. Appreciate it, as I was able to reproduce with something similar, which was enough to warrant the bug fix :)