Ruby on Rails的，通过比较和SQL Server的顺序 [英] Ruby on Rails, SQL Server order by and comparison

问题描述

我有以下查询的麻烦。我不知道为什么。

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

这是用户模型：

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

我试图让该行订单项目历史下令交货日期（命名为与数据库中的列DlvDate这是在OrderHist表）。

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).

我想：

@ user.order_hist_lines.includes（：order_hist）.order（：DlvDate）

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

但它给我

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.

编辑：

我用spickermann的答案。它的工作，但很多数据丢失的某些原因。

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"]]

我得到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"]]

我收到2719

I get 2719

推荐答案

您连接表，特别是与表名不遵守Rails`conventions的时候需要非常precise。

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)

这篇关于Ruby on Rails的，通过比较和SQL Server的顺序的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！