CODEWITHSUNDEEP
ruby-on-railsruby
Bilal Ahmed
Bilal Ahmed

Reputation: 433

IN clause in :conditions in rails

I am working in rails 2, I want to execute Query 

PunchingInformation.all(
  :select => "users.id, login, firstname, lastname, 
    sec_to_time(avg(time_to_sec(punching_informations.punch_in_time))) as 'avg_pit',
    sec_to_time(avg(time_to_sec(punching_informations.punch_out_time))) as 'avg_pot'",
  :joins => :user,
  :group => "users.id",
  :conditions => { 
    "punching_informations.date between '#{start_date}' and '#{end_date}'", 
    ["punching_informations.user_id IN (?)", employees.map { |v| v.to_i } ]
  }
)

But it always return error like

Mysql::Error: Unknown column 'punching_informations.date between '2012-09-01' and '2012-09-25'' in 'where clause': SELECT users.id,login, firstname,lastname, sec_to_time(avg(time_to_sec(punching_informations.punch_in_time))) as 'avg_pit', sec_to_time(avg(time_to_sec(punching_informations.punch_out_time))) as 'avg_pot' FROM punching_informations INNER JOIN users ON users.id = punching_informations.user_id AND (users.type = 'User' OR users.type = 'AnonymousUser' ) WHERE (punching_informations.date between '2012-09-01' and '2012-09-25' IN ('punching_informations.user_id IN (?)','--- \n- 28\n- 90\n')) GROUP BY users.id

Need your help.

Upvotes: 0

Views: 426

Answers (2)

khustochka
khustochka

Reputation: 2386

It is a bit unclear what you meant (you have array, but taken in curly braces {} like a hash), but it seems ruby treats first string ("punching_informations.date between '#{start_date}' and '#{end_date}'") as a column, and second array, as array of expected values, thus making the invalid IN condition.

Perhaps it would work if rewritten as

:conditions => { 
   [ "(punching_informations.date between '#{start_date}' AND '#{end_date}') AND punching_informations.user_id IN (?)", employees.map { |v| v.to_i } ]
 }

or even better

:conditions => { 
   [ "(punching_informations.date between ? AND ?) AND punching_informations.user_id IN (?)", start_date, end_date, employees.map { |v| v.to_i } ]
 }

Upvotes: 1

Rubyman
Rubyman

Reputation: 874

add punching_informations.date and punching_informations.user_id in select

:select => "punching_informations.date, punching_informations.user_id, users.id, ....

Upvotes: -1

Related Questions