CODEWITHSUNDEEP
mysqlsqlruby-on-railsrails-activerecord
iamspauljamez
iamspauljamez

Reputation: 287

Convert SQL "Concat" to Activerecord

I have a SQL query like this:

select * from clients where 'Ed Sheeran' like Concat(Concat('%',first_name),'%') 
or 'Ed Sheeran' like Concat(Concat('%',last_name),'%')

I want to convert my query to active record.

I'd attempted to convert it: 

Client.joins(:cases).where(''Ed Sheeran' LIKE CONCAT(CONCAT('%'first_name, )'%')' OR 'Ed Sheeran' LIKE CONCAT(CONCAT('%'last_name, )'%')')

but I've got this result:

Incorrect parameter count in the call to native function 'CONCAT': SELECT COUNT(*) FROM `clients` INNER JOIN `cases` ON `cases`.`client_id` = `clients`.`id` AND `cases`.`deleted_at` IS NULL WHERE `clients`.`deleted_at` IS NULL AND ('%Ed Sheeran%' LIKE CONCAT(CONCAT()):

Upvotes: 0

Views: 119

Answers (2)

Simone Carletti
Simone Carletti

Reputation: 176382

I may be wrong, but it looks to me that you can easily get rid of the double concat if you rewrite the query to avoid YODA-style from

select * from clients where 'Ed Sheeran' like Concat(Concat('%',first_name),'%') 
or 'Ed Sheeran' like Concat(Concat('%',last_name),'%')

to

SELECT * 
FROM clients 
WHERE 
    first_name LIKE "%Ed Sheeran%" OR
    last_name LIKE "%Ed Sheeran%"

and in ActiveRecord

Client.joins(:cases).where("first_name LIKE :query OR last_name LIKE :query", query: "%Ed Sheeran%")

if that's not the case, this is the other query.

Client.joins(:cases).where("'Ed Sheeran' LIKE CONCAT(CONCAT('%', first_name), '%') OR 'Ed Sheeran' LIKE CONCAT(CONCAT('%', last_name), '%')")

Your query was missing some comma separation.

Upvotes: 2

jpalumickas
jpalumickas

Reputation: 731

Maybe you want to use like this ?

query = 'Ed Sheeran'

Client.where("first_name LIKE :query OR last_name LIKE :query", query: "%#{query}%")

Upvotes: 0

Related Questions