是否可以定义一个 SQL 查询，从一个表中提取一组允许的foreign_keys，然后使用它们来过滤另一个表？

发布于 2024-08-24 07:06:45 字数 568 浏览 9 评论 0原文

具体来说，我试图弄清楚是否可以生成 SQL 来执行我想要输入 Ruby-on-Rails 的 find_by_sql 方法的操作。

想象一下，有一些用户通过连接表 Friendship 循环连接到其他用户。每个用户都可以创建评论。

我想要一个 SQL 查询返回给定用户的任何朋友创建的最新 100 条评论，这样我就可以将它们全部显示在一个方便的位置供用户查看。

这很棘手，因为本质上我希望通过作者的外键是否包含在从用户朋友的主键派生的一组键中来过滤评论。

编辑：澄清设置。我不太确定如何编写模式定义，因此我将用 Rails 来描述它。

class User
  has_many :friends, :through => :friendships
  has_many :comments
end
class Friendship
  belongs_to :user
  belongs_to :friend, :class_name => "User", :foreign_key => "friend_id"
end
def Comment
  has_one :User
end

原文

Specifically, I'm trying to figure out if it's possible to generate SQL that does what I want to feed into Ruby-on-Rails' find_by_sql method.

Imagine there are Users, who are joined cyclically to other Users by a join table Friendships. Each User has the ability to create Comments.

I'd like a SQL query to return the latest 100 comments created by any friends of a given user so I can display them all in one convenient place for the user to see.

This is tricky, since essentially I'm looking to filter the comments by whether their foreign keys for their author are contained in a set of keys obtained derived from the user's friends' primary keys.

Edit: Clarifying the setup. I'm not exactly sure how to write a schema definition, so I'll describe it in terms of Rails.

class User
  has_many :friends, :through => :friendships
  has_many :comments
end
class Friendship
  belongs_to :user
  belongs_to :friend, :class_name => "User", :foreign_key => "friend_id"
end
def Comment
  has_one :User
end

分享到QQ

分享到微博

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

发布评论

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

醉殇 2024-08-31 07:06:45

这并不复杂，你只需使用连接即可。要仅获取评论，您只需加入“友谊”表和“评论”表，但您可能还需要从“用户”表中获取撰写评论的人的一些信息。

这将从 id 为 42 的用户的好友中获取最后 100 条评论：

select top 100 c.CommentId, c.CommentText, c.PostDate, u.Name
from Friendships f
inner join Users u on u.UserId = f.FriendUserId
inner join Comments c on c.UserId = u.UserId
where f.UserId = 42
order by c.PostDate desc

It's not that tricky, you just use joins. To get just the comments you only need to join the Friendships table and the Comments table, but you probably also want some information from the Users table for the person who wrote the comment.

This would get the last 100 comments from people who are friends with the user with id 42:

select top 100 c.CommentId, c.CommentText, c.PostDate, u.Name
from Friendships f
inner join Users u on u.UserId = f.FriendUserId
inner join Comments c on c.UserId = u.UserId
where f.UserId = 42
order by c.PostDate desc

回复收藏 0 原文

~没有更多了~