Search fails with Postgres about craft-retour HOT 5 CLOSED

Fixed in 62c3936

from craft-retour.

Interesting.

from craft-retour.

This is what the query looks like:

        // Query the db table
        $offset = ($page - 1) * $per_page;
        $query = (new Query())
            ->from(['{{%retour_static_redirects}}'])
            ->offset($offset)
            ->limit($per_page)
            ->orderBy("{$sortField} {$sortType}");
        if ($filter !== '') {
            $query->where("`redirectSrcUrl` LIKE '%{$filter}%'");
            $query->orWhere("`redirectDestUrl` LIKE '%{$filter}%'");
        }

I'm a little confused, as I am specifying the table in the from?

from craft-retour.

Couple things to consider with PostgreSQL:

• The column quote char is ", not a backtick like MySQL
• Column names are normalized to lowercase unless correctly quoted.

So that’s why your redirectSrcUrl column is showing up as redirectsrcurl in the SQL error.

Easiest way to deal with it is to use Yii’s like query builder condition syntax, which will auto-quote the column names for you with the correct characters, and auto-add %'s to the beginning/end of the search value by default.

if ($filter !== '') {
    $query->where(['like', 'redirectSrcUrl', $filter]);
    $query->orWhere(['like', 'redirectDestUrl', $filter]);
}

See the QueryInterface::where() API docs for more info on the condition syntaxes Yii supports.

from craft-retour.

Ahhh this makes sense. Thanks @brandonkelly

from craft-retour.