Ruby on Rails, SQL Server Query

Question

I am having trouble with the following queries. I am not sure why.

This is the user model:

class UserProfile < ActiveRecord::Base

  self.table_name = "UserProfile"
  self.primary_key = "UserId"

  has_many :user_access_job_list, class_name: 'UserAccessJobList', foreign_key: 'UserId'
  has_many :job_tables, class_name: 'JobTable', through: :user_access_job_list
  has_many :order_histories, class_name: 'OrderHist', through: :job_tables
  has_many :order_hist_lines, class_name: 'OrderHistLine', through: :job_tables
  has_many :delivery_histories, class_name: 'DeliveryHist', through: :job_tables    

end

This is the order line item history model:

class OrderHistLine < ActiveRecord::Base

  self.table_name = "OrderHistLine"
  self.primary_key = "WebLineId"

  belongs_to :job_table, class_name: 'JobTable', foreign_key: 'JobId'
  belongs_to :job_product, class_name: 'JobProduct', foreign_key: 'ItemId'
  belongs_to :order_hist, class_name: 'OrderHist', foreign_key: 'WebOrderId'
  has_many :delivery_histories, class_name: 'DeliveryHist', foreign_key: 'WebLineId'

end

I am trying to query the order line item history for a specific product name. ItemFreeTxt is the name of the column in job product for the product name. I am using the following query:

@user.order_hist_lines.joins(:job_product).where(ItemFreeTxt: "xxxxxxxxxxx")

This gives me the error:

ActiveRecord::StatementInvalid: TinyTds::Error: Invalid column name 'ItemFreeTxt'.: EXEC sp_executesql N'SELECT [OrderHistLine].* FROM [OrderHistLine] INNER JOIN [JobProduct] ON [JobProduct].[ItemId] = [OrderHistLine].[ItemId] INNER JOIN [JobTable] ON [OrderHistLine].[JobId] = [JobTable].[JobId] INNER JOIN [UserAccessJobList] ON [JobTable].[JobId] = [UserAccessJobList].[JobId] WHERE [UserAccessJobList].[UserId] = @0 AND [OrderHistLine].[ItemFreeTxt] = N''1''', N'@0 nvarchar(10)', @0 = N'LamCK'

However it works when I search using the I search using the ItemId instead of the product name.

@user.order_hist_lines.joins(:job_product).where(ItemId: "SI000110")

I can get unique product names and item ids from the following queries without any problems:

@user.order_hist_lines.includes(:job_product).pluck(:ItemId).uniq
@user.order_hist_lines.includes(:job_product).pluck(:ItemFreeTxt).uniq

So I am not sure why I can't query for the ItemFreeTxt is not working. Any advice or help is greatly appreciated. Thank you very much in advance. Let me know if I need to provide more information.

spickermann · Accepted Answer · 2015-11-22 11:20:41Z

1

You need to add the table name to the query:

@user.order_hist_lines.joins(:job_product).
  where(JobProduct: { ItemFreeTxt: "xxxxxxxxxxx" })

edited Nov 22, 2015 at 11:20

answered Nov 22, 2015 at 9:23

spickermann

108k9 gold badges115 silver badges147 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Kelvin Over a year ago

it's giving me this error: ActiveRecord::StatementInvalid: TinyTds::Error: The multi-part identifier "job_product.ItemFreeTxt" could not be bound.: EXEC sp_executesql N'SELECT [OrderHistLine].* FROM [OrderHistLine] INNER JOIN [JobProduct] ON [JobProduct].[ItemId] = [OrderHistLine].[ItemId] INNER JOIN [JobTable] ON [OrderHistLine].[JobId] = [JobTable].[JobId] INNER JOIN [UserAccessJobList] ON [JobTable].[JobId] = [UserAccessJobList].[JobId] WHERE [UserAccessJobList].[UserId] = @0 AND [job_product].[ItemFreeTxt] = N''CEW 20/20D,75 PFA (MAX. W/C 0.60)''', N'@0 nvarchar(10)', @0 = N'LamCK'

spickermann Over a year ago

@ShinonChan : It is a bit hard to understand that error message, because of your table names do not follow Rails' conventions. I updated the where clause in my answer. The hash structure depends on the table names.

Kelvin Over a year ago

it worked!! thank you so much. yeah, I have to read a database that is from a microsoft erp system so the naming is quite confusing for me too.

Kelvin Over a year ago

spickermann, can you help me with this as well? stackoverflow.com/questions/33855625/…

Collectives™ on Stack Overflow

Ruby on Rails, SQL Server Query

1 Answer 1

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related