Ruby on Rails, SQL Server order by and comparison

问题:

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 get the line order item history ordered by delivery date (the column named used in the database is DlvDate which is in the OrderHist table).

I tried:

@user.order_hist_lines.includes(:order_hist).order(:DlvDate)

but it's giving me

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

Also I need to find orders between a to and from date. I did:

@user.order_hist_lines.includes(:order_hist).where("DlvDate < ?", to_date).where("DlvDate > ?", from_date)

which is giving me the same error.

EDIT:

I used spickermann's answer. it worked but a lot of data is missing for some reason.

@user.order_hist_lines.includes(:order_hist).order('OrderHist.DlvDate DESC').count

EXEC sp_executesql N'SELECT COUNT(DISTINCT [OrderHistLine].[WebLineId]) FROM [OrderHistLine] LEFT OUTER JOIN [OrderHist] ON [OrderHist].[WebOrderId] = [OrderHistLine].[WebOrderId] INNER JOIN [JobTable] ON [OrderHistLine].[JobId] = [JobTable].[JobId] INNER JOIN [UserAccessJobList] ON [JobTable].[JobId] = [UserAccessJobList].[JobId] WHERE [UserAccessJobList].[UserId] = @0', N'@0 nvarchar(10)', @0 = N'LamCK'  [["UserId", "LamCK"]]

I get 9

@user.order_hist_lines.includes(:order_hist).count

EXEC sp_executesql N'SELECT COUNT(*) FROM [OrderHistLine] INNER JOIN [JobTable] ON [OrderHistLine].[JobId] = [JobTable].[JobId] INNER JOIN [UserAccessJobList] ON [JobTable].[JobId] = [UserAccessJobList].[JobId] WHERE [UserAccessJobList].[UserId] = @0', N'@0 nvarchar(10)', @0 = N'LamCK'  [["UserId", "LamCK"]]

@user.order_hist_lines.count

EXEC sp_executesql N'SELECT COUNT(*) FROM [OrderHistLine] INNER JOIN [JobTable] ON [OrderHistLine].[JobId] = [JobTable].[JobId] INNER JOIN [UserAccessJobList] ON [JobTable].[JobId] = [UserAccessJobList].[JobId] WHERE [UserAccessJobList].[UserId] = @0', N'@0 nvarchar(10)', @0 = N'LamCK'  [["UserId", "LamCK"]]

I get 2719

回答1:

You need to be very precise when joining tables and especially with table names not following Rails`conventions.

I think the following should work:

@user.order_hist_lines.includes(:order_hist).
  order('OrderHist.DlvDate')

Same for the other query:

@user.order_hist_lines.includes(:order_hist).
  where('OrderHist.DlvDate BETWEEN ? AND ?', from_date, to_date)