How to chain where conditions with "or" in Rails 4?

Question

In my Rails 4 app I need to find all plans that do either have an interval of month OR an amount of 0.

This doesn't work:

class Plan < ActiveRecord::Base def self.by_interval(interval) where("interval = ? OR amount = ?", interval, 0) end end

I am getting this error:

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 '= 'month' OR amount = 0) ORDER BY amount DESC' at line 1: SELECT `plans`.* FROM `plans` WHERE (interval = 'month' OR amount = 0) ORDER BY amount DESC

What else might work?

Thanks for any help.

I think you have a type issue. Would have been helpfull if you posted the syntax error. I guess your interval is a string. so try: where("interval like '?' OR amount = ?", interval, 0) — Sander Garretsen
– Sander Garretsen, Commented Jan 1, 2015 at 20:58
Tried that but didn't work. The syntax errors I am getting all look similar: 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 'LIKE ''month'' OR amount = 0)' at line 1: SELECT plans.* FROM plans WHERE (interval LIKE ''month'' OR amount = 0) — Tintin81
– Tintin81, Commented Jan 1, 2015 at 21:09
sorry, remove the single quotes again and update your question with the original syntax error please. — Sander Garretsen
– Sander Garretsen, Commented Jan 1, 2015 at 21:18
What datatype is interval? Is it a kind of string/text/varchar? — Ruby Racer
– Ruby Racer, Commented Jan 1, 2015 at 21:58

Sander Garretsen · Accepted Answer · 2015-01-01 22:07:39Z

2

'interval' in mysql is a reserved word (http://dev.mysql.com/doc/refman/5.6/en/reserved-words.html).

Try it like this:

def self.by_interval(interval) where("`interval` = ? OR amount = ?", interval, 0) end

note the backticks around "interval" (not quotes)

answered Jan 1, 2015 at 22:07

Sander Garretsen

1,72310 silver badges19 bronze badges

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

2 Comments

Tintin81 Over a year ago

Wow, surprisingly that finally works! Thank you. I just wonder if I should better rename the column to something else if interval is a reserved word.

Sander Garretsen Over a year ago

Looks like this is only a problem if you write your queries like this yourself. If you do things like "where(:interval => interval)" Rails already handles it in the generated sql. So I don't think renaming is necessary.

6ft Dan · Accepted Answer · 2015-01-01 22:27:52Z

Since you're not doing an exclusive or, but an inclusive, I would do it in two requests:

class Plan < ActiveRecord::Base def self.by_interval(interval) where(interval: interval) << where(amount: 0) end end

These are both arrays of results and the second set of results get injected into the first. I do realize this is two separate requests so it might not be as optimized as you'd like.

I believe using Rails ActiveRecord caching may be a way to save on a performance hit. I don't know if it's done automatically for you in this case, or if you should load the full table request before the queries are performed.

boulder_ruby · Accepted Answer · 2015-01-01 20:20:05Z

-1

Just pass the arguments directly into the strings

def self.by_interval(interval) where("interval = #{interval} OR amount = 0") end

answered Jan 1, 2015 at 20:20

boulder_ruby

40k9 gold badges82 silver badges104 bronze badges

4 Comments

boulder_ruby Over a year ago

I'm not sure why this answer was downvoted, but if your interval variable is a string you just have to wrap it with single quotes

Tintin81 Over a year ago

Thanks, but both suggestions didn't work. I keep getting MySQL syntax errors.

Tom Harrison Over a year ago

@boulder_ruby I didn't downvote, but perhaps one reason is that adding interpolated values in SQL could result in SQL Injection vulnerabilities. I think your answer is correct, but would be improved by where("interval = ? OR amount = 0", interval) to avoid this issue.

boulder_ruby Over a year ago

I know this isn't a good answer, but maybe "Switch to PostgreSQL" (or SQLite3) is your best solution

Collectives™ on Stack Overflow

How to chain where conditions with "or" in Rails 4?

3 Answers 3

2 Comments

Comments

4 Comments

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

Comments

4 Comments

Related