how to query for activerecord's select_value method?

Question

can anyone please tell me how to write query in select_value.

I have tried,

    ActiveRecord::Base.connection.select_value("select count(*) from leave_details where status= 'Pending' and 'employeedetails_id'=25")

but it showing error

   invalid input syntax for integer: "employeedetails_id".

I am using PostgreSQL.

Answer 1

Single quotes are used to quote strings in PostgreSQL (and every other SQL database that even pretends to respect the SQL standard) so you're saying something like this:

some_string = some_integer

when you do this:

'employeedetails_id'=25

and that doesn't make any sense: you can't compare strings and integers without an explicit type cast. You don't need to quote that identifier at all:

ActiveRecord::Base.connection.select_value(%q{
    select count(*)
    from leave_details
    where status = 'Pending'
      and employeedetails_id = 25
})

If you even do need to quote an identifier (perhaps it is case sensitive or contains spaces), then you'd use double quotes with PostgreSQL.

---

Apparently you created your column as "EmployeeDetails_id" so that it is case sensitive. That means that you always have to use that case and you always have to double quote it:

ActiveRecord::Base.connection.select_value(%q{
    select count(*)
    from leave_details
    where status = 'Pending'
      and "EmployeeDetails_id" = 25
})

I'd recommend reworking your table to not use mixed case identifiers:

1. They go against standard Ruby/Rails naming.
2. They force you to double quote the mixed case column names everywhere you use them.
3. They go against standard PostgreSQL practice.

This is going to trip you up over and over again.

Answer 2

Executing SQL directly isn't really The Rails Way, and you lose any database portability by doing it that way.

You should create a model for leave_details. E.g.

rails g model LeaveDetails status:string employeedetails_id:integer

Then, the code would be:

LeaveDetails.where({ :status => 'Pending', :employeedetails_id => 25 }).count