Rails ActiveRecord：使用 LEFT JOIN 而不是 INNER JOIN 进行连接

发布于 2024-08-06 16:17:15 字数 493 浏览 6 评论 0原文

我有这段

User.find(:all, :limit => 10, :joins => :user_points,
                :select => "users.*, count(user_points.id)", :group =>
                "user_points.user_id")

生成以下sql的

SELECT users.*, count(user_points.id) 
FROM `users` 
INNER JOIN `user_points` 
ON user_points.user_id = users.id 
GROUP BY user_points.user_id 
LIMIT 10

代码，是否可以使用User.find_by_sql以外的方式进行LEFT JOIN而不是INNER JOIN并手动输入查询？

原文

I have this code

User.find(:all, :limit => 10, :joins => :user_points,
                :select => "users.*, count(user_points.id)", :group =>
                "user_points.user_id")

which generates following sql

SELECT users.*, count(user_points.id) 
FROM `users` 
INNER JOIN `user_points` 
ON user_points.user_id = users.id 
GROUP BY user_points.user_id 
LIMIT 10

is it possible to make LEFT JOIN instead of INNER JOIN other way than User.find_by_sql and manualy typing the query?

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

圈圈圆圆圈圈 2024-08-13 16:17:15

你可以试试这个

User.find(:all, limit: 10,
            joins:  "LEFT JOIN `user_points` ON user_points.user_id = users.id" ,
            select: "users.*, count(user_points.id)", 
            group:  "user_points.user_id")

You can try this

User.find(:all, limit: 10,
            joins:  "LEFT JOIN `user_points` ON user_points.user_id = users.id" ,
            select: "users.*, count(user_points.id)", 
            group:  "user_points.user_id")

回复收藏 0 原文

喵星人汪星人 2024-08-13 16:17:15

仅供将来参考，添加 :all 会给出一条已弃用的消息。在 Rails 的更高版本中，您可以像这样简单地链接方法：

User.joins("LEFT JOIN `user_points` ON user_points.user_id = users.id").select("users.*, count(user_points.id)").group("user_points.user_id")

或者使用像这样的 scope：

scope :my_scope_name_here, -> { 
        joins("LEFT JOIN `user_points` ON user_points.user_id = users.id")
        .select("users.*, count(user_points.id)")
        .group("user_points.user_id")
}

您还可以在 .join.where code> 和 .select。
希望这对将来的人有帮助。

Just for future reference, adding :all gives a deprecated message. In later versions of rails you can simply chain the methods like this:

User.joins("LEFT JOIN `user_points` ON user_points.user_id = users.id").select("users.*, count(user_points.id)").group("user_points.user_id")

OR use a scope like this:

scope :my_scope_name_here, -> { 
        joins("LEFT JOIN `user_points` ON user_points.user_id = users.id")
        .select("users.*, count(user_points.id)")
        .group("user_points.user_id")
}

You can also chain .where between the .join and the .select.
Hope this helps someone in the future.

回复收藏 0 原文