What to do if you get a "Mysql2 :: Error: Operand should contain 1 column (s)" error in Rails

Hello everybody, is @ ryosk7.

Status

I will enter from the main subject quickly.

Mysql2::Error: Operand should contain 1 column(s)

What to do if you get an error like this.

As for the situation,

user_ids = @current_user.sample_contents.pluck(:to_user_id)

@samples = SampleModel.where('
                              (user_id = ? and status = ?) or (user_id = ? and status is not null)',
                              user_ids, SampleModel.statuses[:open], @current_user.id)
                      .order(id: "DESC")

It was output when I wrote such code.

Solutions

it's here.

user_ids = @current_user.sample_contents.pluck(:to_user_id)

@samples = SampleModel.where('
                              (user_id = ? and status = ?) or (user_id = ? and status is not null)',
                              user_ids.join(","), SampleModel.statuses[:open], @current_user.id)
                      .order(id: "DESC")

Did you notice the difference?

The array is changed to a string concatenated with commas.

user_ids.join(",")

Cause

If you use Active Record, it will be recognized without any problem even if you pass it as an array to user_id. The cause was that I was doing the same on SQL. If it's a single value, it's okay, but if it's multiple values, you need to pass a list of strings.

Actually, SQL is created like this. The place converted by join is the same. I'm not strong in SQL, so I learned a lot.

SELECT `sample_models`.* FROM `sample_models` WHERE ((user_id = '10,23,55' and status = 0) or (user_id = 14 and status is not null))  ORDER BY `sample_models`.`id` DESC

In the reference article, I'm hitting a query using IN. If you write in the same way,

user_ids = @current_user.sample_contents.pluck(:to_user_id)

@samples = SampleModel.where('
                              (user_id in (?) and status = ?) or (user_id = ? and status is not null)',
                              user_ids.join(","), SampleModel.statuses[:open], @current_user.id)
                      .order(id: "DESC")

It looks like this.

SQL is also created with IN.

SELECT `sample_models`.* FROM `sample_models` WHERE ((user_id in ('10,23,55') and status = 0) or (user_id = 14 and status is not null))  ORDER BY `sample_models`.`id` DESC

Both give the same result.

reference

https://stackoverflow.com/questions/10012695/sql-statement-using-where-clause-with-multiple-values

Recommended Posts

What to do if you get a "Mysql2 :: Error: Operand should contain 1 column (s)" error in Rails
What to do if you get a gcc error in Docker
What to do if you get a DISPLAY error in gym.render ()
What to do if you get a "302" error in your controller unit test code in Rails
What to do if you get a java.io.IOException in GlassFish
What to do if you get an error in Basic authentication during Rails test code
What to do if you get a groovy warning in Thymeleaf Layout
What to do if you get an error during rails db: reset
What to do if you get a JNI shared library error when trying to build in Eclipse
What to do if you get a SQLite3 :: BusyException: database is locked error
What to do if mysql2 gets a bundle install error
What to do if you get a port error when docker-compose up on Mac
What to do if you get an "A server is already running." Error when you try to start the rails server
[Rails] What to do if you can't get parameters with form_with
[Rails Tutorial Chapter 2] What to do when you make a mistake in the column name
What to do if you get a "Cannot Pull Container Error" when starting ECS ​​Fargate
[Rails] What to do if you can't get an error message with the errors method
What to do if you get an error saying "Please enter a valid value" when getting with Rails datetime_field
What to do if you get an error saying "Could not find a JavaScript runtime." When starting rails server
[Rails] What to do if you get an error saying "Could not find a JavaScript runtime." When executing the rails s command on Catalina
What to do if you get an uninitialized constant Likes Controller error
What to do if you get an [An HTTP request took too long to complete.] Error in Docker.
What to do if you get an error when you hit Heroku logs
What to do if you get a MiniMagick vulnerability alert on GitHub
What to do if you get an error with bundle install by entering gem'bcrypt' in your Gemfile
What to do if you should have installed Rails but an error occurs with rails -v (for beginners)
What to do if the image posted by refile disappears after setting a 404 error page in Rails
What to do if you get the error message unrecognized selector send to instance "***"
What to do if you get an error on heroku rake db: migrate
What to do if you accidentally create a model
What to do if you get `rescue in block in modules_for_hoge': Missing hoge file hoge / hoge_hoge.rb (AbstractController :: Helpers :: MissingHogeError) in rails s even though the file is in the specified error location.
What to do if you get a javax.net.ssl.SSLHandshakeException: Remote host closed connection during handshake in the IBM JDK
What to do if you get Could not save master table to file after importing a project in Eclipse
What to do if you can't get the text of an element in Selenium
What to do if you get a NoClassDefFoundError when trying to run eclipse on Java9
What to do if you get the error Couldn't find Item without an ID
# What to do if you accidentally do rails db: migrate: drop
What to do if you can't use the rails command
[Java] What to do if you get an error in Eclipse saying "Not allowed at source level below 1.X"
What to do if you get an Argument Error: wrong number of arguments (given 2, expected 0) in your RSpec test
What to do if you get the warning "Uniqueness validator will no longer enforce case sensitive comparison in Rails 6.1." in Rails 6.0
What to do if you don't see the test code error message in the terminal console
[Rails] What to do if you accidentally install bundle in the production environment in your local environment
[Rails] What to do if data is not registered in DB
What to do if the Rails page doesn't appear in Rails tutorial 1.3.2
How to make a jar with old Hadoop (hadoop-core-0.20.2-cdh3u6) in Gradle: (What to do if you get Could not expand ZIP ..)
What to do if Cloud9 is full in the Rails tutorial
What to do if you get angry with OpenSSL with pyenv install
What to do if you forget the root password in CentOS7
<Dot installation> What to do if you cannot proceed due to an error when building a development environment for Rails learning.
What to do if you select a JRE in Eclipse and get "The selected JRE does not support the current compliance level 11"
What to do if you get angry with "ERROR: There are no scenarios; must have at least one" even though you should have entered yarn in Docker
[Programming beginner] What to do when rails s becomes an error in the local development environment
What to do if you install Ubuntu
If you leave the Next.js app open in Chrome, you will get a "/ _next / webpack-hmr" Routing error in the Rails app.
What to do if you can't bundle update and bundle install after installing Ruby 3.0.0 in the Rails tutorial
What to do if Operation not permitted is displayed when you execute a command in the terminal
What to do if you get the error Too long with no output (exceeded 10m0s) on CircleCI
What to do if you get an error saying Cannot find a valid baseurl for repo when you use yum -y install docker in a udemy course using CentOS7
What to do if you push incorrect information
What to do if you get Could not locate Gemfile or .bundle / directory