How to query array column from mysql in Rails 5.2?

Question

I am using gem rails~> 5.2 and gem mysql2 >= 0.3.13, < 0.5.

I have a model Lawer, which has an array column lawer_filed [sic].

# Model lawer.rb

serialize :lawer_field, Array

Then I created a Lawer, and I can get the lawer_field value as follows:

=> Lawer.first.lawer_field

=> ["2", "3", "5"]

Now, I want to find one Lawer with a query using lawer_field. I tried:

@lawer = Lawer.where("lawer_field && ARRAY[?]", "2")

which raised an error like this:

ActiveRecord::StatementInvalid (Mysql2::Error: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '['2']) LIMIT 11' at line 1: SELECT  `lawers`.* FROM `lawers` WHERE (lawer_field && ARRAY['2']) LIMIT 11)

There is a mistake in my SQL syntax, but I don't how to fix it. Can anyone help?

I am not sure whether i am right or wrong but "2" should be in array . Can you try this one @lawer = Lawer.where("lawer_field && ARRAY[?]", ["2"]) ` — Vishal
– Vishal, Commented Nov 28, 2018 at 12:19
I also try as your codes @Vishal, I got the error like this: ActiveRecord::StatementInvalid (Mysql2::Error: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '['2']) LIMIT 11' at line 1: SELECT lawers.* FROM lawers` WHERE (lawer_field && ARRAY['2']) LIMIT 11)`, it does not work either. — SylorHuang
– SylorHuang, Commented Nov 28, 2018 at 12:23
Lawer.where("lawer_field AND ARRAY[?]", "2") have you tried this? — Anand
– Anand, Commented Nov 28, 2018 at 12:59

Marcin Kołodziej · Accepted Answer · 2018-11-28 14:03:06Z

2

MySQL, unlike PostgreSQL, does not support arrays in database. Therefore you needed to add this line:

serialize :lawer_field, Array

This means that you have a string field in your database, but whenever ActiveRecord is unpacking results returned by the database, it maps them directly to an instance of Ruby Array. What this means is that your only option to filter the results in the database is with any MySQL string comparison functions, LIKE, etc.

Your options are to either use LIKE or perform some other String functions (which will not perform well as you will be unable to use indices) or build another table, add a has_many association to it and use MySQL the way it was supposed to be used. You could also, of course, migrate to PostgreSQL, but that seems to be the most extreme option.

EDIT: you could also consider using MySQL`s JSON, which has been added recently. That depends on your version of MySQL though.

edited Nov 28, 2018 at 14:03

answered Nov 28, 2018 at 13:58

Marcin Kołodziej

5,3231 gold badge12 silver badges18 bronze badges

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

3 Comments

SylorHuang Over a year ago

I had tried LIKE function like this : Lawer.all.where("lawer_field LIKE ? ","2"), it returns nil array.What's wrong with the codes, and How to write correctly the codes? Could you teach me ? Thanks so much.

Marcin Kołodziej Over a year ago

See: stackoverflow.com/questions/19105706/…

SylorHuang Over a year ago

That is great! Thanks a lot. It works. @Marcin Kołodziej. Next step , I will consider change MySQL to PostgreSQL.

Efesto · Accepted Answer · 2018-11-28 12:22:11Z

-1

I didn't try this answer because I don't have any Rails project ready for testing, but I think the problem is in the syntax.

I think it should be something like this:

Lawer.where("lawer_field IN (?)", "2")

edited Nov 28, 2018 at 12:22

answered Nov 28, 2018 at 12:15

Efesto

1371 silver badge7 bronze badges

5 Comments

SylorHuang Over a year ago

I had try as your code @Efesto, but I got the same error:

ActiveRecord::StatementInvalid (Mysql2::Error: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''2') LIMIT 11' at line 1: SELECT

lawers.* FROM lawers` WHERE (lawer_field IN '2') LIMIT 11)`, Thanks any way.

Efesto Over a year ago

I edited the code because I think the brackets are necessary, maybe give it another shot now ;)

SylorHuang Over a year ago

With the edited code, I didn't got syntax error, but it returns a nil array, the result is : ` => #<ActiveRecord::Relation []> `, this is for why? Thanks @Efesto.

Efesto Over a year ago

I just checked and MYSQL doesn't seem to implement the array type, it could be useful for you to implement a serializable field for storing the array as in this answer: stackoverflow.com/questions/28685984/rails-arrays-and-mysql or directly to build another table.

SylorHuang Over a year ago

Thanks any way, @Efesto. I had serializable the field in Lawer.rb, serialize :lawer_field, Array. Maybe I had to build another table for lawer_field.

Collectives™ on Stack Overflow

How to query array column from mysql in Rails 5.2?

2 Answers 2

3 Comments

5 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

5 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related