How can I successfully to use limit() method with subqueries with Rails and SQL Server Adapter

Question

Actual behavior

It seems that limit() doesn't add to the generated subquery which actually is a Model::ActiveRecord_Relation, instead tries an execution which throws the error:

ActiveRecord::StatementInvalid Exception table doesn't exists

Code and how to get the issue

class Doc < ApplicationRecord
  self.table_name = "D_docs"

  has_many :doc_dirs

  has_many :dirs, through: :doc_dirs

  default_scope -> { where(inactive: false, is_version_1: true)}

  scope :doc_collection, -> do
    str_sql = "
        (
          SELECT [D_docs].[fileType], [D_docs].[inactive], [D_docs].[isVisible]
          FROM [D_docs]
        ) [D_docs]
    "
    doc_collec = Doc.from(str_sql).limit(10)
    # byebug
    doc_collec
  end
end

On rails console if you add a debug between doc_collec = Doc.from(str_sql).limit(10) and doc_collec

running doc_collec.to_sql you will get:

ActiveRecord::StatementInvalid Exception: Table '( SELECT [D_docs].[fileType], [D_docs].[inactive], [D_docs].[isVisible] FROM [D_docs] ) [D_docs]' doesn't exist

it should be noted that Doc.from(str_sql).to_sql is giving: SELECT [D_docs].* FROM ( SELECT [D_docs].[fileType], [D_docs].[inactive], [D_docs].[isVisible] FROM [D_docs] ) [D_docs]

A correct and without errors of Doc.from(str_sql).limit(10) is working on older rails versions (6.1.7) and SQL Server adapter version 6.x.x successfully generating the query:

SELECT [D_docs].* FROM ( SELECT [D_docs].[fileType], [D_docs].[inactive], [D_docs].[isVisible] FROM [D_docs] ) [D_docs] OFFSET 0 FETCH NEXT 10 ROWS ONLY

Also Doc.from(str_sql).order(:pkid) works as expected

The versions:

• Rails version: 7.1.2
• SQL Server adapter version: 7.1.0
• TinyTDS version: 2.1.5

---

UPDATE

Thanks to @engineersmnky

After changing doc_collec = Doc.from(str_sql).limit(10) to

doc_collec = Doc.from(Arel::Nodes::TableAlias.new(Arel::Nodes::TableAlias.new(Doc.select(:fileType,:inactive,:isVisible).arel, self.table_name))).limit(10)

I'm getting:

SELECT [D_docs].* FROM (SELECT [D_docs].[fileType], [D_docs].[inactive], [D_docs].[isVisible] FROM [D_docs]) [D_docs] ORDER BY [D_docs].[docName] ORDER BY [D_docs][pkid] OFFSET 0 ROWS FETCH NEXT 10 ROWS ONLY

which works (have to change the query); now I'd like to prevent the order by [D_docs].[pkid] if is possible, the reason not pkid is needed bc I'm planning to add an union query, how can I do it?

Answer 1

Based on your post I am making the following assumption (as it is the only way that SQL code be generated as stated)

class Doc < ApplicationRecord
  self.table_name = "D_docs"
end

That being said we can construct your desired functionality as follows:

 scope :doc_collection, -> do
    Doc.from(
      Arel::Nodes::TableAlias.new(Doc.select(:docName,:docType).arel, self.table_name)
    ).order(:docName).limit(10)
  end

This will result in:

SELECT [D_docs].* FROM (SELECT [D_docs].[docName], [D_docs].[docType] FROM [D_docs]) [D_docs] ORDER BY [D_docs].[docName] OFFSET 0 ROWS FETCH NEXT 10 ROWS ONLY

Note by excluding the selection of the primary key you will not be able to relate this data to other associations such as doc_dirs, please ensure this is your explicit intention or simply add pkid to the select list above.

Discourse

Your question is unclear as to: (Answered in comments)

• What [D_docs] is, since it seems like it that table does not exist?

A: [D_docs] is as you're using

Response: You should probably add this to your post

• Why you need a subquery since you are selecting an entire table?

A: The subquery actually is selecting specific values, [D_docs].docName and [D_docs].doctype and other values from associated tables (not worth mentioning)

Response: This would have been beneficial context to the original post. Additionally the not worth mentioning is probably worth mentioning.

• How you intend to utilize this with an alias of [D_docs]?

A: After your comment an alias seems to work but when the limit() method is added the query a order by [D_doc].[pkid] which is invalid bc the query only fetches docName and docType

Response: You cannot have a limit without the order by because MSSQL Limit uses OFFSET and FETCH and needs to know how to order so it can offset consistently

Answer 2

Following suggestions given by @engineersmnky I was able to get the query working as expected, noticed also I've added the .order('') so doing doc_collec.to_sql debugging before doc_collec it's returned the generated query:

SELECT [D_docs].* FROM (SELECT [D_docs].[docName], [D_docs].[docType] FROM[D_docs]) [D_docs] OFFSET 0 ROWS FETCH NEXT 10 ROWS ONLY

New modified scope:

scope :doc_collection, -> do
  doc_collec = Doc.from(
    Arel::Nodes::TableAlias.new(Arel::Nodes::TableAlias.new(Doc.select(:fileType,:inactive,:isVisible).arel, self.table_name))
  ).order('').limit(10)
  # byebug
  doc_collec
end

Thanks a lot for the help