Not compatible with PostgreSQL?

[Marcuzz]

Hi,

When using this plugin with PostgreSQL we seem to be encountering a lot of errors with several SQL queries. For example:

https://github.com/born05/craft-assetusage/blob/dc6b7575ef710498be684150be959254d5147ecd/src/console/controllers/DefaultController.php#L71-L72

The columns in PostgreSQL are transformed to lowercase unless they are wrapped in double quotes, and PostgreSQL is case-sensitive. For these queries to work the column has to be wrapped in double quotes, i.e: relations."targetId"

There is another issue on the following lines as well:

https://github.com/born05/craft-assetusage/blob/dc6b7575ef710498be684150be959254d5147ecd/src/console/controllers/DefaultController.php#L77-L78

There is a type incompatible comparison here. "LIKE" is only possible on text/strings, but "content" is a jsonb column.

There are more similar PostgreSQL errors, here: https://github.com/born05/craft-assetusage/blob/dc6b7575ef710498be684150be959254d5147ecd/src/services/Asset.php#L92-L93

I'm not sure what the best way to fix this is, while keeping compatibility with MySQL as well, so I figured I'd just create an issue as opposed to a PR.

[cherrykoda]

Same issue, installed fine but is erroring on the usage of "LIKE"

[niektenhoopen]

+1

SQLSTATE[42883]: Undefined function: 7 ERROR:  operator does not exist: jsonb ~~ unknown
LINE 3: WHERE ("content" LIKE $1) OR ("content" LIKE $2)
                         ^
HINT:  No operator matches the given name and argument types. You might need to add explicit type casts.
The SQL being executed was: SELECT "elementId" AS "id", "siteId"
FROM "elements_sites"
WHERE ("content" LIKE '%asset:27923:%') OR ("content" LIKE '%"imageId": "27923",%')

[niektenhoopen]

@Marcuzz @cherrykoda Can you please test https://github.com/born05/craft-assetusage/pull/51 ?

@roelvanhintum Can you verify that this still works on MySQL?