I got stuck trying to write a where in clause in ActiveRecord

I understand that it is written in SQL, but I was worried about how to write it in Rails, so I will write it

What I wanted to know

SELECT * FROM Tables WHERE (name, task) not in ((yamada, "running"),(yamada, "swimming"));

I had a lot of trouble, but I couldn't put it together well, so I solved it with a subquery! If you pass it as an array, you can pass multiple items, and if you make a subquery like this, you can create the conditions you want!

Queries are issued as expected


Table.where.not(id: (Table.select(:id).where(name: yamada).where(task: ["running", "swimming"])))

Initial policy

In the first policy, I thought that I could narrow down the conditions by repeating the where clause multiple times, but if I write the following, I will play all the tasks of "yamada". The SQL that is actually issued is also described below. I wrote while drawing a Venn diagram in my head.

A query different from the expected value is issued


Table.where.not(name: yamada).where.not(task: ["running", "swimming"])

Queries different from expected value


SELECT `tables`.* FROM `tables` WHERE `tables`.`deleted_at` IS NULL AND (`tables`.`name` != "yamada") AND (`tables`.`task` NOT IN ("running", "swimming"))

References

Rails Guide (https://railsguides.jp/active_record_querying.html)

Recommended Posts

I got stuck trying to write a where in clause in ActiveRecord
Where I got stuck in today's "rails tutorial" (2020/10/05)
Where I got stuck in today's "rails tutorial" (2020/10/04)
Where I got stuck in today's "rails tutorial" (2020/10/07)
I got stuck in a clone of a two-dimensional array
A funny story stuck in a mess when trying to import fx-clj
What if I write a finally clause in the try-with-resources syntax?
I want to write a nice build.gradle
I want to write a unit test!
[JAVA] Project Euler, I got stuck in Q8, so make a note
I wanted to make (a == 1 && a == 2 && a == 3) true in Java
I want to simply write a repeating string
I made a sample of how to write delegate in SwiftUI 2.0 using MapKit
Where I got stuck with Swift Package Manager support
I want to use a little icon in Rails
I got a cannot resolve symbol in Android Studio
I tried to create a Clova skill in Java
I tried to make a login function in Java
I want to define a function in Rails Console
I want to click a GoogleMap pin in RSpec
I got an error when trying to install sbt to build a Scala development environment
How to write a core mod in Minecraft Forge 1.15.2
When I switched to IntelliJ, I got a lot of differences in the encoding of the properties file.
[Beginner] I made a program to sell cakes in Java
I want to make a specific model of ActiveRecord ReadOnly
How to create a placeholder part to use in the IN clause
I just wanted to make a Reactive Property in Java
A story I was addicted to in Rails validation settings
I tried to convert a string to a LocalDate type in Java
I tried to implement a buggy web application in Kotlin
I tried to make a client of RESAS-API in Java
I wrote a code to convert numbers to romaji in TDD
I tried to create a simple map app in Android Studio
I tried to illuminate the Christmas tree in a life game
A solution to Docker errors that beginners tend to get stuck in
[Ruby] I want to put an array in a variable. I want to convert to an array
I got an IllegalAccessError when trying to use PowerMock with JUnit
[jOOQ] How to CASE WHEN in the WHERE / AND / OR clause
I got a Permission Denied error when I put Laravel in Docker
I think it's okay to write joins etc. in the scope
I got a good punch when trying Hello World in Scala (object java.lang.Object in compiler mirror not found.)
[Rails] How to write in Japanese
I created a PDF in Java.
How to write a ternary operator
Get stuck in a Java primer
I built a rails environment with docker and mysql, but I got stuck
I tried to make a parent class of a value object in Ruby
I want to select multiple items with a custom layout in Dialog
Even in Java, I want to output true with a == 1 && a == 2 && a == 3 (PowerMockito edition)
I tried to build a Firebase application development environment with Docker in 2020
I wanted to implement a slide show in a fashionable way with slick.
I want to display a PDF in Chinese (Korean) with thin reports
I thought about the best way to create a ValueObject in Ruby
I tried to make a talk application in Java using AI "A3RT"
I want to ForEach an array with a Lambda expression in Java
"Teacher, I want to implement a login function in Spring" ① Hello World
How to reference a column when overriding the column name method in ActiveRecord
When registering a new user, I got an error called ActiveRecord :: NotNullViolation and how to deal with it.