CODEWITHSUNDEEP
ruby-on-railssql-serverrubyactiverecordactive-record-query
Kelvin
Kelvin

Reputation: 2288

Ruby on Rails, SQL Server Query

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.

Upvotes: 0

Views: 193

Answers (1)

spickermann
spickermann

Reputation: 107107

You need to add the table name to the query:

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

Upvotes: 1

Related Questions