Rails Advance 查询 CASE WHEN 方法不起作用 [英] Rails Advance query CASE WHEN method not working

问题描述

当我使用完美运行的 mysql 时，有没有人可以在这里向我建议 Find_by_sql 查询的解决方案.但是当我将其转换为活动记录时，它没有显示确切的结果.首先，我使用 find_by_sql 在下面尝试了此代码:

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 :

@message = Message.select(:user_one, :user_two).distinct.where("user_one = ? OR user_two = ? " , current_user ,current_user)
    @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

这两个查询看起来都一样，但是当我使用 find_by_sql 方法时，我得到了两个结果，但是当我重新查询活动记录时，它只显示一个，当我将生成的 sql 粘贴到 mysql 那里时，我也得到相同的结果如果任何人都可以帮助我了解这两个查询之间的区别以及如何解决这个问题我的意思是我需要在活动记录中找到 find_by_sql 的结果.

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 .

推荐答案

根据查询，您的方案是选择那些 user_one 是当前用户或 user_two 是当前用户的消息.所以不是复杂的查询，你可以简单地做这个

according to the query your scenario is to pick those messages where either user_one is current user or user_two is the current user. so rather than complex query, what you can simply do is this

Message.where("messages.user_one = ? OR messages.user_two =?", current_user, current_user).order("m_id ASC").limit(20)

现在为用户添加细节.您可以遍历视图中的消息并为每条消息获取用户并显示该消息.或者您可以制作散列数组并将其返回到视图.

now to add detail to it for user. you can either traverse the messages in view and fetch the user for each message and display that. or you can make array of hashes and return that to the view.

这篇关于Rails Advance 查询 CASE WHEN 方法不起作用的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！