[Bug] Group games with same name but different tags as versions

[zeroSteiner]

RomM version 4.2.0

Describe the bug Revision tags can be inserted into file names. These tags show up as versions for a game in the UI and helpful dropdown menu is presented to allow the user to switch between ROM versions and even set a default. This feature however does not seem to work for games that are not matched in any configured database such as IGDB. This can often be the case when it's a game from a small indie publisher.

To Reproduce

1. Take a ROM file that doesn't exist in any database (a lot of games on itch.io).
2. Tag it with a revision like Game (Rev v1.1).rom and a second Game (Rev v1.2).rom.
3. Place both in the library under the appropriate platform and rescan the library.
4. See that both files are named after their filename. Edit the metadata so they both have the same title, rescan and see that they are still not identified as multiple versions of the same game. Place them in a folder with the game title, and see that they are still not identified as multiple versions of the same game.

Expected behavior I was hoping that the games would be grouped together as other versioned ROMs are regardless of if they're found in an online database or not.

Additional context I looked into this bug a bit and found that part of the problem is that the ROM files are not considered sibilings as defined by the sibiling_roms view defined here. I updated that to also match when all IDs are NULL and the titles and platforms match. That caused the ROMs to show up as versions however each ROM file still shows up as a different game in the library still.

Hacked sibiling_roms View Definition

select `r1`.`id`                                                                          AS `rom_id`,
       `r2`.`id`                                                                          AS `sibling_rom_id`,
       `r1`.`platform_id`                                                                 AS `platform_id`,
       current_timestamp()                                                                AS `created_at`,
       current_timestamp()                                                                AS `updated_at`,
       case when `r1`.`igdb_id` <=> `r2`.`igdb_id` then `r1`.`igdb_id` end                AS `igdb_id`,
       case when `r1`.`moby_id` <=> `r2`.`moby_id` then `r1`.`moby_id` end                AS `moby_id`,
       case when `r1`.`ss_id` <=> `r2`.`ss_id` then `r1`.`ss_id` end                      AS `ss_id`,
       case when `r1`.`launchbox_id` <=> `r2`.`launchbox_id` then `r1`.`launchbox_id` end AS `launchbox_id`,
       case when `r1`.`ra_id` <=> `r2`.`ra_id` then `r1`.`ra_id` end                      AS `ra_id`,
       case when `r1`.`hasheous_id` <=> `r2`.`hasheous_id` then `r1`.`hasheous_id` end    AS `hasheous_id`,
       case when `r1`.`tgdb_id` <=> `r2`.`tgdb_id` then `r1`.`tgdb_id` end                AS `tgdb_id`
from (`romm`.`roms` `r1` join `romm`.`roms` `r2`
      on (`r1`.`platform_id` = `r2`.`platform_id` and `r1`.`id` <> `r2`.`id` and
          ((`r1`.`igdb_id` = `r2`.`igdb_id` and `r1`.`igdb_id` is not null or
           `r1`.`moby_id` = `r2`.`moby_id` and `r1`.`moby_id` is not null or
           `r1`.`ss_id` = `r2`.`ss_id` and `r1`.`ss_id` is not null or
           `r1`.`launchbox_id` = `r2`.`launchbox_id` and `r1`.`launchbox_id` is not null or
           `r1`.`ra_id` = `r2`.`ra_id` and `r1`.`ra_id` is not null or
           `r1`.`hasheous_id` = `r2`.`hasheous_id` and `r1`.`hasheous_id` is not null or
           `r1`.`tgdb_id` = `r2`.`tgdb_id` and `r1`.`tgdb_id` is not null) or
           (`r1`.`igdb_id` is null and `r2`.`igdb_id` is null and `r1`.`moby_id` is null and `r2`.`moby_id` is null and
           `r1`.`ss_id` is null and `r2`.`ss_id` is null and `r1`.`launchbox_id` is null and
           `r2`.`launchbox_id` is null and `r1`.`ra_id` is null and `r2`.`ra_id` is null and
           `r1`.`hasheous_id` is null and `r2`.`hasheous_id` is null and `r1`.`tgdb_id` is null and
           `r2`.`tgdb_id` is null and `r1`.`fs_name_no_tags` <=> `r2`.`fs_name_no_tags`))))

[gantoine]

I looked into this bug a bit and found that part of the problem is that the ROM files are not considered sibilings as defined by the sibiling_roms

That's correct, we only consider matched games as siblings if they have a metadata ID in common. I suppose we could extend that definition to any games in the same platform with the same fs_name_no_tags, but that would have to be discussed. @zurdi15 thoughts?

[zurdi15]

Having more options and being more flexible is always a good thing. If a file has literally the same name I think it's safe to assume it's the same game, but we need to discuss how that is going to impact or conflict with hasheous