Fix AmbiguousColumn error in Orderable concern

Moaz Khalifarequested to merge
578343-fix-packages_nuget_symbol_states-pk into master

What does this MR do and why?

Fixes AmbiguousColumn error in the Orderable concern when used with models that have separate verification state tables.

Changes made:

  • Refactor Orderable concern: Updated order_by_primary_key scope to use arel_table[primary_key] instead of just primary_key to avoid SQL ambiguity when joining tables with the same column names
  • Clean up PackagesNugetSymbolState model: Removed unnecessary self.primary_key = :packages_nuget_symbol_id assignment. It was a copy-past leftover, which isn't correct since the PK of the table is the id column.
  • Enhance test factories: Improved NuGet symbol factory traits to properly set up verification states and associations for testing
  • Add unit tests: Added test coverage for data management API with models that use separate verification state tables

Why this change is needed:

The AmbiguousColumn error occurs when ActiveRecord queries involve joins between tables that have columns with the same name (like id). By using arel_table[primary_key], we explicitly specify which table's primary key column to use, eliminating the ambiguity. This is particularly important for Geo replication features where verification state tables are joined with their parent models.

References

Screenshots or screen recordings

N/A

How to set up and validate locally

  1. Set up a local Geo environment with a primary and secondary node. Follow the Geo development setup guide.

  2. Hit the data_management API:

curl -H "Authorization: Bearer <token>" "http://localhost:3000/api/v4/admin/data_management/packages_nuget_symbol?checksum_state=succeeded"

It should return an empty array, which means no errors. On master, it will result in a 500 with this error in the logs: ActiveRecord::StatementInvalid (PG::AmbiguousColumn: ERROR: column reference "id" is ambiguous

MR acceptance checklist

Evaluate this MR against the MR acceptance checklist. It helps you analyze changes to reduce risks in quality, performance, reliability, security, and maintainability.

Related to #578343 (closed)

Edited by Moaz Khalifa

Merge request reports