sql 左连接 where Rails

发布于 2024-09-25 07:19:26 字数 494 浏览 9 评论 0原文

因为我使用rails，所以我对sql变得生疏了，因为我很少在rails中使用它。我有两个相关的表： comments 1:m comment_views

我想查找 comment_views.viewed 为 false 的所有评论。问题是，对于某些评论，comment_views 中还没有相关记录。

到目前为止，我已经

select comments.id 
    from comments 
        left join comment_views 
            on comments.id = comment_views.comment_id 
    where comment_views.viewed != "t" 
    group by type_id, object_id 
    order by comments.created_at desc

但是如上所述，当 comment_views 中没有记录时，不会返回评论。

原文

Because I use rails I've become rusty on sql since I rarely use it in rails.
I have two related tables:
comments 1:m comment_views

I want to find all comments where comment_views.viewed is false. Problem is, for some comments there is not a relating record in comment_views yet.

So far I have

select comments.id 
    from comments 
        left join comment_views 
            on comments.id = comment_views.comment_id 
    where comment_views.viewed != "t" 
    group by type_id, object_id 
    order by comments.created_at desc

But as stated, that doesn't return comments when there is no record in comment_views.

分享到QQ

分享到微博

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

发布评论

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

隔纱相望 2024-10-02 07:19:26

如果没有记录，您可以检查是否为null...

where comment_views.viewed != "t" or comments_views.viewed is null

You can check for null if there is no record...

where comment_views.viewed != "t" or comments_views.viewed is null

回复收藏 0 原文

鹿港小镇 2024-10-02 07:19:26

一些注释：

一旦您在 where 子句中引用左连接表 (comment_views) 中的列，您就会强制连接充当内部连接。将这些条件改为连接。
不知道你为什么要进行分组。我认为没有必要。

所以，代码应该是：

select comments.id 
    from comments 
        left join comment_views 
            on comments.id = comment_views.comment_id 
                and comment_views.viewed != "t"
    order by comments.created_at desc

A couple of comments:

As soon as you reference a column from your left-joined table (comment_views) in the where clause, you force the join to act as if it was an inner join. Put those conditions on the join instead.
Not sure why you're doing the group by. I don't think it's needed.

So, the code should be:

select comments.id 
    from comments 
        left join comment_views 
            on comments.id = comment_views.comment_id 
                and comment_views.viewed != "t"
    order by comments.created_at desc

回复收藏 0 原文

~没有更多了~