Rails MySQL query using where statement

Question

I have this users:

NAME _____ ROLE_________STATUS

user1 -------- role1 ---------------- true

user2 -------- role2 ---------------- true

user3 -------- role2 ---------------- false

user4 -------- role3 ---------------- false

user5 -------- role4 ---------------- true

I have this query:

User.where("role = ? OR role = ? OR role = ? AND status = ?", "role1", "role2", "role3", true)

and i expect that the data will be retrieve is:

user1

user2

but the system retrieve the user3 even if his status is false.

I also try to change AND to OR but it retrieves all.

what's wrong about my query?

Eyeslandic · Accepted Answer · 2014-06-27 10:27:10Z

2

What's wrong with the query is the lack of parentheses around the 'OR groups'

User.where("(role = ? OR role = ? OR role = ?) AND status = ?", "role1", "role2", "role3", true)

However, as already stated in another answer, it can be done in an easier way like this.

User.where(status: true).where(role: %w{role1 role2 role3})

answered Jun 27, 2014 at 10:27

Eyeslandic

14.9k13 gold badges44 silver badges55 bronze badges

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

Comments

Pranav Dhar · Accepted Answer · 2014-06-27 10:20:11Z

1

try this :

User.where(status: true).where(role: ["role1", "role2", "role3"])

answered Jun 27, 2014 at 10:20

Pranav Dhar

213 bronze badges

Comments

martincarlin87 · Accepted Answer · 2014-06-27 10:26:55Z

1

Try using parentheses to group the conditions:

User.where("(role = ? OR role = ? OR role = ?) AND status = ?", "role1", "role2", "role3", true)

The tricky bit might be the true, is it just stored as a string? e.g. a varchar or text column?

As an alternative, you can also use the IN operator

User.where("role IN (?, ?, ?) AND status = ?", "role1", "role2", "role3", true)

answered Jun 27, 2014 at 10:26

martincarlin87

11.1k24 gold badges104 silver badges148 bronze badges

1 Comment

BartSabayton Over a year ago

User.where("role IN (?, ?, ?) AND status = ?", "role1", "role2", "role3", true) ---> works in me! Thanks for the answer :)

Collectives™ on Stack Overflow

Rails MySQL query using where statement

3 Answers 3

Comments

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related