1

The following query runs fine on a postgres client (postico):

sql = "select rs.created_at, lv.id
from external_verification_responses rs
inner join external_verification_requests rq on rs.external_verification_request_id = rq.id
inner join leave_verifications lv on lv.id = rq.request_id
WHERE (rs.params#>>'{last_call_Trip Enquiry_1, outcome}' = 'Interested') AND (date(rs.created_at) >= '2018-08-21')
ORDER BY rs.created_at DESC"

However, with rails, it breaks

records_array = ActiveRecord::Base.connection.execute(sql)

The error is:

ActiveRecord::StatementInvalid: PG::SyntaxError: ERROR:  syntax error at or near "select rs"
LINE 1: select rs.created_at, lv.id from external_verification_respo...

If defining alias is a problem in rails raw sql query, then how do I define alias in my query?

strivedi183
  • 4,749
  • 2
  • 31
  • 38
cop
  • 93
  • 3
  • 10
  • 2
    Well, you have a syntax error. Can you post exactly the commands you're using? Where you define `sql`. If that's the first one, then please format it properly to be valid ruby syntax (quotes and so) – dgilperez Aug 28 '18 at 07:07
  • sql is ofcourse a string with double quotes. I have edited in the question. However, i 'm still getting error – cop Aug 28 '18 at 07:10
  • What's the `rs.params#>>'{last_call_Trip Enquiry_1, outcome}'` part? Also, did you try removing the alias and using `external_verification_responses.created_at` instead? – dgilperez Aug 28 '18 at 07:10
  • 1
    To diagnose it would you mind to try it with `sql = "select rs.created_at from external_verification_responses rs limit 1"`? – Aleksei Matiushkin Aug 28 '18 at 07:11
  • Please refer the link . U will find solution. – Lavanya Aug 28 '18 at 07:13
  • @dgilperez this part is json query, as said it runs fine on sql clients. I also tried without alias but same error – cop Aug 28 '18 at 07:16
  • @mudasobwa This short sql works, however breaks on inner join – cop Aug 28 '18 at 07:22
  • @LavanyaAnanya how is the linked answer relevant? – Aleksei Matiushkin Aug 28 '18 at 07:22
  • 1
    GUYS, as it turns out, there could have been a special character impersonating space character in the query as I was copying the query directly from sql client(postico) into rails console. When I typed the query all by myself it worked. Thanks for your support – cop Aug 28 '18 at 08:00

0 Answers0