Input custom value from parameters to Rails SQL Query

Question

I have written so code to query lists of users who go jogging at a certain time and in certain locations, but I am getting the following error: ActiveRecord::StatementInvalid: SQLite3::SQLException: near "'jogging'"

Does this mean I cannot write a string into that variable? Are there any solutions to this?

users = User.where('ids in (:user_ids)', user_ids: 
             Activity.where('title :title AND created_at >= :created_at AND location_id: location_id', 
             {title: 'jogging', created_at: Time.zone.now.beginning_of_day, location_id: 
             Location.where('id in id', id: user.activities.where.not(location_id: nil)
            .order('created_at DESC').first)}))

Tip: don't write long queries in one huge line. It's very easy to become lost and stuck at errors like that. Split this huge query into smaller pieces using intermediate variables, and you will be able to see the syntax error by your own. — nattfodd
– nattfodd, Commented Feb 1, 2018 at 8:52
@nattfodd could you give me some examples, I'm new to this. Thank you. — rony
– rony, Commented Feb 1, 2018 at 10:15

Ajay Barot · Accepted Answer · 2018-02-01 12:13:15Z

You can simplified your query this way

location_id = Location.where(
                             id: user.activities
                                     .where.not(location_id: nil)
                                     .order('created_at DESC').first
                            )

user_ids    = Activity.where("title = ? AND created_at > ? AND location_id = ?", 
                             "jogging", Time.zone.now.beginning_of_day, location_id)

users       = User.where(id: user_ids)

But If you want to keep query one liner. You can use this

User.where('id IN(:user_ids)', 
            user_ids:  Activity.where('title = :title AND created_at >= :created_at AND location_id = :location_id', title: 'jogging', created_at: Time.zone.now.beginning_of_day, 
            location_id: Location.where('id IN(:id)', id: user.activities.where.not(location_id: nil)
            .order('created_at DESC').first).ids)
          )

Note: I am assuming that you have user object to use above one liner query

Hope this will help you.

siegy22 · Accepted Answer · 2018-02-01 10:20:57Z

1

I think you forgot to add an = in the query:

Your query:

Activity.where('title :title ...

What you want:

Activity.where('title = :title ...

And if you don't need an operator like > or <, you can also use:

Activity.where(title: title)

If you then need to chain it, it's pretty simple:

Activity.where(title: title).where('foo < ?', 100)

answered Feb 1, 2018 at 10:20

siegy22

4,4433 gold badges27 silver badges44 bronze badges

2 Comments

rony Over a year ago

should I add = to everyquery? because I have location_id also

siegy22 Over a year ago

@chronycles yes, I think you should lookup SQL query syntax.

Collectives™ on Stack Overflow

Input custom value from parameters to Rails SQL Query

2 Answers 2

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related