Ruby on Rails, SQL Server order by and comparison

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

Please run rake db:migrate, rake schema:dump and edit the question with the contents of db/schema.rb — hd1
– hd1, Commented Nov 22, 2015 at 14:06

spickermann · Accepted Answer · 2015-11-22 14:15:03Z

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)

answered Nov 22, 2015 at 14:15

spickermann

108k9 gold badges115 silver badges147 bronze badges

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

6 Comments

Kelvin Over a year ago

thanks! please see edit, a lot of my data is missing for some reason.

Kelvin Over a year ago

any ideas why this is happening?

spickermann Over a year ago

I am sorry. Rails adds a COUNT(DISTINCT [OrderHistLine].[WebLineId]) when you count the records. That is very strange and I cannot explane why.

Kelvin Over a year ago

would you mind taking a look at the code if I sent you all the code?

spickermann Over a year ago

I think the problem is that you have two different associations to class_name: 'OrderHist', one as order_histories in the UserProfile, another as order_hist in OrderHistLine. It seems like you have cyclic dependency here and Rails' include leads to confusing results.

|

Collectives™ on Stack Overflow

Ruby on Rails, SQL Server order by and comparison

1 Answer 1

6 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related