Is there anyone who can suggest me the solution for a Find_by_sql query here when I am using mysql that work perfect . But when I converted that into active record It is not showing the exact result . First I have tried this code below using find_by_sql:
@message3 = Message.find_by_sql(["SELECT u.id,c.m_id,u.name,u.email FROM messages c, users u WHERE (CASE WHEN c.user_one = :id THEN c.user_two = u.id WHEN c.user_two = #{current_user.id} THEN c.user_one= u.id END ) AND ( c.user_one ='1' OR c.user_two ='1' ) Order by c.m_id DESC Limit 20", {:id => current_user.id}]) When I am using this query I am getting the exact value which I want but when I am doing this thing in active record way it is not working well SO here is the code which I have tried in active record style : @m1 = Message.joins(:user).select("users.id,messages.m_id,users.name,users.email").where("CASE WHEN messages.user_one = #{current_user.id} THEN messages.user_two = users.id WHEN messages.user_two = #{current_user.id} THEN messages.user_one = users.id END").where("messages.user_one = 1 OR messages.user_two = 1 ").order("messages.m_id DESC") When I am running this query I am getting this generate query : SELECT users.id,messages.m_id,users.name,users.email FROM `messages` INNER JOIN `users` ON `users`.`id` = `messages`.`user_one` WHERE (CASE WHEN messages.user_one = 1 THEN messages.user_two = users.id WHEN messages.user_two = 1 THEN messages.user_one = users.id END) AND (messages.user_one = 1 OR messages.user_two = 1 ) ORDER BY messages.m_id DESC Both these query look same but when I am using find_by_sql method I am getting two results but when I am reunnig query of active record it is only showing one when I paste the generated sql in mysql there also I am getting same result IF anyone can help me to understand the difference between these two queries and how can I fix this I mean I need the result of find_by_sql in active record . -- Posted via http://www.ruby-forum.com/. -- You received this message because you are subscribed to the Google Groups "Ruby on Rails: Talk" group. To unsubscribe from this group and stop receiving emails from it, send an email to rubyonrails-talk+unsubscr...@googlegroups.com. To post to this group, send email to rubyonrails-talk@googlegroups.com. To view this discussion on the web visit https://groups.google.com/d/msgid/rubyonrails-talk/4845b8b4ae7e033911510f6612028541%40ruby-forum.com. For more options, visit https://groups.google.com/d/optout.