How do I write SQL with format 'SELECT ... FROM ... WHERE ... IN ( SELECT ... )' in rails class model scope?

Question

How do I write SQL with

SELECT * FROM tableA WHERE tableA.col1 IN ( SELECT tableB.col2 FROM tableB )

in Rails model scope?

Currently, I have a SQL like this in ruby model file as class method:

class Book def self.select_list_for_current_project_fund_schemes_sponsor_name Books.connection.select_all(' SELECT book.name, book.name FROM BOOK b WHERE b.b_pk IN ( SELECT s.b_fk FROM STORE s ) ').rows end end

It works, and produce the output I want:

Book.select_list_for_current_project_fund_schemes_sponsor_name => [[book_name1, book_name1], [book_name2, book_name2], [book_name3, book_name3]...]

But I want to write it in scope so it is consistent with other code.

How do I write the SQL above using ActiveRecord 'where' method in the class model scope?

I want something like this in the class's model file:

class Book scope :books_in_store_that_exist, -> { where(magic_sql_wrapped_in_ruby_here) } # more code here... end

Note: I don't have model for Store, I only have Book model.

In other words, I want to be able to achieve the same output by writing

Book.books_in_store_that_exist.select(:name).map {|b| [b.name, b.name]} => [[book_name1, book_name1], [book_name2, book_name2], [book_name3, book_name3]...]

Deepak Mahakale · Accepted Answer · 2018-06-18 05:54:06Z

1

In that case, you just need to add a inner join

class Book scope :books_in_store_that_exist, -> { joins("INNER JOIN stores ON books.b_pk = stores.b_fk") } end

Now you can use it to chain it.

Book.books_in_store_that_exist.select(:name).map { |b| [b.name, b.name] } #=> [[book_name1, book_name1], [book_name2, book_name2], [book_name3, book_name3]...]

answered Jun 18, 2018 at 5:54

Deepak Mahakale

23.8k10 gold badges73 silver badges94 bronze badges

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

3 Comments

Henry Yang Over a year ago

Thank you for the solution, however I realise I have duplicate resulting rows, do you happen to know how to get rid of the duplicates? I was looking up Google and saw DISTINCT but it seems to work on SELECT and not JOIN.

Deepak Mahakale Over a year ago

Try this Book.books_in_store_that_exist.distinct(:name).select(:name).map { |b| [b.name, b.name] }

Henry Yang Over a year ago

For future people, you can also use where like this: scope :books_in_store_that_exist, -> { where("b.b_pk IN (SELECT s.b_fk FROM STORE s))" }

Collectives™ on Stack Overflow

How do I write SQL with format 'SELECT ... FROM ... WHERE ... IN ( SELECT ... )' in rails class model scope?

1 Answer 1

3 Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Related