CODEWITHSUNDEEP
ruby-on-railsruby-on-rails-3
loganathan
loganathan

Reputation: 6136

How to remove quotes in active record query

I am trying to retrieve records from multiple tables based on collection of ids suing the below query, it is working fine.

ACTIVE RECORD QUERY:

Song.order("songs.id desc"). includes(:artist).includes(:album). joins("LEFT JOIN artists ON songs.artist_id = artists.id"). joins("LEFT JOIN albums ON songs.album_id = albums.id"). where("(songs.id)

IN (#{song_ids*','})

AND (LOWER(songs.title) LIKE :term OR LOWER(artists.name) LIKE :term OR LOWER(albums.title) LIKE :term)", :term => "%#{terms[index].downcase}%")

PG QUERY OUTPUT:

SELECT "songs".* FROM "songs" LEFT JOIN artists ON songs.artist_id = artists.id LEFT JOIN albums ON songs.album_id = albums.id WHERE (

(songs.id) IN (4,5,28,37,46,48)

AND (LOWER(songs.title) LIKE '%wo%' OR LOWER(artists.name) LIKE '%wo%' OR LOWER(albums.title) LIKE '%wo%')) ORDER BY songs.id desc

But while I was trying to modify the query as below

ACTIVE RECORD QUERY:

Song.order("songs.id desc"). includes(:artist).includes(:album). joins("LEFT JOIN artists ON songs.artist_id = artists.id"). joins("LEFT JOIN albums ON songs.album_id = albums.id"). where("(songs.id)

IN (:song_ids)

AND (LOWER(songs.title) LIKE :term OR LOWER(artists.name) LIKE :term OR LOWER(albums.title) LIKE :term)",

      :term => "%#{terms[index].downcase}%",:song_ids => song_ids*',')

PG QUERY OUTPUT:

SELECT "songs".* FROM "songs" LEFT JOIN artists ON songs.artist_id = artists.id LEFT JOIN albums ON songs.album_id = albums.id WHERE (

(songs.id) IN ('4,5,28,37,46,48')

AND (LOWER(songs.title) LIKE '%wo%' OR LOWER(artists.name) LIKE '%wo%' OR LOWER(albums.title) LIKE '%wo%')) ORDER BY songs.id desc

Because of the single quotes the above query doesn't working.

Upvotes: 3

Views: 734

Answers (1)

Ermin Dedovic
Ermin Dedovic

Reputation: 907

Try replacing :song_ids => song_ids*',' with :song_ids => song_ids.

In your original code, you converted song_ids to string, which was escaped and single quotes were put to mark that it was a string. This passes only array as an argument and rails will do the magic and convert it to something like this 4,5,28,37,46,48 (you still need your parentheses).

Upvotes: 1

Related Questions