Recent changes break the plugin if using non-Postgres db

[jihyukbae]

Bug report

Describe the bug

A recent fix for making this plugin compatible with localization #47 provided by @Fikoun includes multiple instances of SELECT DISTINCT ON, which is a Postgres-specific query that does NOT work with other databases like MySQL.

Our project is using MySQL, so these queries are considered syntactically invalid and cause fatal errors whenever they are triggered (e.g. when updating an entry).

Although the queries would become more verbose, it would be ideal to replace them with non-db-specific ones (and also avoid introducing db-specific queries in the future) so that devs are not limited to using a specific db like Postgres for their Strapi project if they want to use this plugin.

Steps to reproduce the behavior

1. Use plugin version 1.0.0-beta.0 to apply the recent changes
2. Create a new content type with versioning and internationalization enabled
3. Create a new entry of this type and save (this works)
4. Update the entry and click "Save" or "Save as a new version".
5. Saving fails, with error along the lines of error: SELECT DISTINCT ON (locale) id, version_number, published_at, locale FROM tests WHERE vuid='88b6e1e7-c360-4a24-b1e3-5460493b1c3c' ORDER BY locale, published_at DESC NULLS LAST, version_number DESC - near "ON": syntax error

Expected behavior

The updated entry is successfully saved, whether in the current version or in a new version.

Code snippets

Instances of SELECT DISTINCT ON added in #47: 1 2 3

System

• Node.js version: 14.20.0
• NPM version: 8.18.0
• Strapi version: 4.6.0
• Database: MySQL
• Operating system:

[martincapek]

Hi, can you test feature/sql-engines-support? It should be fixed here :)

[omikulcik]

As no one seems to be having this problem anymore it was probably fixed by #68 and I am closing this issue.