[RUBY] [Rails] How to write when making a subquery

I want to stream SQL by subquery (nested)

Check with the example of checking duplicate data.

Form a subquery

** users table **

id name address
1 sasaki tokyo
2 itou fukuoka
3 fujita nagasaki
4 sasaki osaka
5 itou nagano

For example, suppose you have a table like the one above. I want to retrieve records with duplicate names and check the address.

sql1: Select one with duplicate name


SELECT name FROM users GROUP BY name HAVING  count(name) > 1

result

name
itou
sasaki

In this case, we do not know the address of each, so based on this result, execute SQL as follows.

sql2: Based on duplicate results


SELECT * FROM users WHERE name IN ('itou', 'sasaki') ORDER BY name

result

id name address
2 itou fukuoka
5 itou nagano
1 sasaki tokyo
4 sasaki osaka

As shown above, each address can also be obtained.

So sql1 and sql2 are combined into a subquery.

sql3: Subquery (sql1,sql2）


SELECT * FROM users WHERE name IN (
  SELECT name FROM users GROUP BY name HAVING  count(name) > 1
  ) 
ORDER BY name

Implemented in Rails

Conclusion

Can be written as

Any controller


duplicates = User.select(:name).group(:name).having("count(name) > 1")
@users = User.where(name: duplicates)

SQL to be executed

SQL executed by Rails


 SELECT "users".* FROM "users" WHERE "users"."name" IN (
   SELECT "users"."name" FROM "users" GROUP BY "users"."name" 
   HAVING (count(name) > 1)
 )

Surprisingly, only one SQL is issued.

Reference (quite easy to understand!) Handling subqueries with the IN operator of ActiveRecord (Oakbow)

Recommended Posts

[Rails] How to write when making a subquery
How to write Rails
How to write Rails validation
How to write Rails seed
How to write Rails routing
How to write a date comparison search in Rails
[Rails] How to write in Japanese
How to write a ternary operator
Rails on Tiles (how to write)
[Rails] How to write exception handling?
How to write a migration from Rails datetime type to date type
[Basic] How to write a Dockerfile Self-learning ②
How to insert a video in Rails
[Introduction to Java] How to write a Java program
How to resolve errors when installing Rails 5.1.3
[rails] How to create a partial template
[SpringBoot] How to write a controller test
[Reading impression] "How to learn Rails, how to write a book, and how to teach"
How to write the view when Vue is introduced in Rails?
[Rails] How to create a graph using lazy_high_charts
How to write dockerfile
How to uninstall Rails
How to write docker-compose
How to write Mockito
How to implement a like feature in Rails
How to easily create a pull-down in Rails
[Rails] How to create a Twitter share button
How to write migrationfile
How to make a follow function in Rails
When defining a class, write formatTo as well as toString (how to use Formattable)
[Rails] How to create a signed URL for CloudFront
How to implement a like feature in Ajax in Rails
[Ruby on Rails] How to write enum in Japanese
How to write a unit test for Spring Boot 2
How to delete a new_record object built with Rails
java: How to write a generic type list [Note]
How to manually generate a JWT with Rails Knock
What to do when rails creates a 〇〇 2.rb file
[How to insert a video in haml with Rails]
[Rails 6] How to set a background image in Rails [CSS]
[Rails] How to load JavaScript in a specific view
How to write a core mod in Minecraft Forge 1.15.2
How to get started with creating a Rails app
[Rails] How to install a decorator using gem draper
[rails] How to post images
How to write good code
[Rails] How to use enum
Bit Tetris (how to write)
[Rails] How to install devise
[Rails] How to use enum
How to write java comments
How to leave a comment
How to read rails routes
[Refactoring] How to write routing
Great poor (how to write)
[Note] How to write Dockerfile/docker-compose.yml
How to use rails join
How to write Junit 5 organized
How to terminate rails server
[Rails] How to use validation
To write a user-oriented program (1)