3

I'm working with a database I have no control over, and cannot make alterations to. This database has a table called warehouse_items. Each warehouse item is uniquely identified by a primary key indicating the item id.

Unfortunately, that primary key attribute is named WAREHOUSE_ITEM.ID

(Note the obnoxious period between "item" and "id")

When I try to run a basic query, such as:

WarehouseItem.find('wh3453')

I get an Undefined Table error.

Fortunately, when looking at what Rails is attempting to do, the problem becomes obvious:

: SELECT  "warehouse_items".* FROM "warehouse_items" WHERE "WAREHOUSE_ITEM"."ID" = $1 LIMIT $2

Because of the period in the attribute name, Rails is treating "WAREHOUSE_ITEM.ID" as a table/attribute combination, rather than an attribute name with a period in it.

When I run the following PSQL query by hand, I get exactly what I need:

SELECT  "warehouse_items".* FROM "warehouse_items" WHERE "warehouse_items"."WAREHOUSE_ITEM.ID" = 'wh3453'

Why is Rails screwing this up, and how can I fix it?

EDIT:

Also worth noting: I've tried using self.primary_key to override the primary key to no avail.

I've tried both a string and a symbol, as in:

self.primary_key="WAREHOUSE_ITEM.ID"

and

self.primary_key=:"WAREHOUSE_ITEM.ID"

Neither one has worked...

Adam Templeton
  • 4,467
  • 7
  • 27
  • 39
  • 1
    Does this answer your question? [How to override the default primary key column in ruby on rails 4.0.+?](https://stackoverflow.com/questions/35731467/how-to-override-the-default-primary-key-column-in-ruby-on-rails-4-0) – Stu Aug 12 '21 at 21:43
  • Unfortunately, no. I've tried using both `self.primary_key="WAREHOUSE_ITEM.ID"` and `self.primary_key=:"WAREHOUSE_ITEM.ID"` (i.e. as a string and as a symbol) to no avail. – Adam Templeton Aug 12 '21 at 21:55
  • 1
    There is another solution I have seen used (not Activerecord but with other orm tools) but might be a no-go given your initial statement; you could create a *view* in the database to the base table in question and alias the column to have the expected `Id` name. – Stu Aug 12 '21 at 21:57
  • Again, I don't have control over the primary database, annoying as that is. However, your comment has made me wonder if I could use a Rails alias to the same effect. I'll try and report back! – Adam Templeton Aug 12 '21 at 21:59
  • 1
    Have you tried `WarehouseItem.find_by("WAREHOUSE_ITEM.ID" => "wh3453")` or even `WarehouseItem.find_by_sql(the_sql_that_works)`. If either of those queries works, you can abstract them into a `WarehouseItem#custom_find` method – Les Nightingill Aug 13 '21 at 00:52
  • 1
    You can use Arel to construct the query `WarehouseItem.find_by(WarehouseItem.arel_table["WAREHOUSEITEM.ID"].eq(1))` - using a string won't actually work due to how rails interprets them. Unfortantely this is really just the tip of the iceberg and I don't really think you will ever get ActiveRecord to play nice with this schema. – max Aug 13 '21 at 09:34
  • The `find_by` method doesn't work, unfortunately. The `find_by_sql` does, however! But... I found an even better solution (that I'll post below)!!! – Adam Templeton Aug 13 '21 at 14:00

1 Answers1

1

Thanks for all the help, everyone!

A suggestion in the comments to use find_by_sql does work! However, I stumbled onto a different solution that works even better.

First, I aliased the annoying attribute name to something simple: id

alias_attribute :id, :"WAREHOUSE_ITEM.ID"

Notice that it's still a symbol, which is important for the next step.

I then overwrite the primary_key method with a custom function:

  def self.primary_key
    return "id"
  end

Now, when I do WarehouseItem.find('wh3453'), Rails defaults to checking id, which is aliased to the correct symbol and it works as intended!!!

Adam Templeton
  • 4,467
  • 7
  • 27
  • 39