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/.