MySQL/MariaDB/SQLite: DISTINCT CONCAT

This question isn't necessarily just Laravel related, but I'm trying to fetch records, which are distinct by concatenated fields. I need this to work with both MySQL/MariaDB as well as SQLite for testing purposes.

While doing my research, I've found out that SQLite does not have CONCAT function - instead you're using || operator to concatenate items. MySQL on the other hand will not interpret || the same way, but I can always use the conditional statement just to cover both cases.

However, I still cannot get records I'm after - my table consists of:

| id | tagable_id | tagable_type | name | title | description | url | image | hits |

I need get only 4 records that are sorted ASC by number of hits and which are unique using CONCAT(table_id, tagable_type).

I tried already:

DB::table('tags')
->selectRaw("DISTINCT CONCAT(`tagable_id`, '-', `tagable_type`), `id`, `name`, `title`, `description`, `url`, `image`")
->whereIn('name', $tags->toArray())
->where('is_active', 1)
->orderBy('hits');

This however does not return distinct records - it will return all records regardless of the distinct concatenation - that is in MySQL / MariaDB - in SQLite it will tell me no such function: CONCAT.

I also tried:

DB::table('tags')
->selectRaw("CONCAT(`tagable_id`, '-', `tagable_type`) as `identifier`, `id`, `name`, `title`, `description`, `url`, `image`")
->whereIn('name', $tags->toArray())
->where('is_active', 1)
->groupBy('identifier')
->orderBy('hits');

This time MySQL/MariaDB tells me that I need to include other fields in the group by as well tags.id' isn't in GROUP BY, but when I use it with SQLite and replace CONCAT function with (tagable_id || '-' || tagable_type) as identifier - it seem to work.

So at this stage I'm: MySQL: 0 | SQLite: 1

Any help would be much appreciated.

via Chebli Mohamed