Rails 3、HABTM如何带条件查询 [英] Rails 3, HABTM how to query with conditions

问题描述

我有奖项和类别，加入了 Awards_Categories

I have Awards and Categories, joined with Awards_Categories

我有 2 个类别.

我需要找到所有 Category.id = 1 但不是 Category.id =2 的奖项.有些类别有一个或另一个，有些类别只有一个.我想要一个有类别 1 但没有类别 2 的奖项列表.

I need to find all the Awards that have Category.id = 1, but not Category.id =2. Some categories have one or the other, some have just one. I want a list of the Awards that have category 1 but not category 2.

我有一个范围:

scope :in_categories, lambda { |categories|
      joins(:categories).
      where(:awards_categories => { :category_id => categories } ).
      select("DISTINCT awards.*")
    }

这适用于如下查询:

@awardsall = Award.in_categories([1 && 2]).order("name ASC") 

我试过了

@awards_store = Award.in_categories([1]).order("name ASC") 

与:

<% @awards_store.each do |store| %> 
        <li><%= link_to store.name, award_path(store), :title => store.info %> | 
        <% store.categories.each do |cat| %>
            <%= cat.id%>
        <% end %>
    </li>
<% end %>

编辑---我知道块不是我需要的.这只是我试图找到一种让它发挥作用的方法.

EDIT--- I know the block is not what I need. it is just my attempt at finding a way to make it work.

虽然这列出了所有奖项和所有奖项类别，但它仍然抓住了 category.id = 2 的奖项，因为有些奖项同时具有

And while this lists all the awards, and all the award categories its still grabbing awards that have category.id = 2 because some awards have both

有什么想法吗?

推荐答案

抱歉，没有测试，但主要思路是计算连接表中的行数.

Sorry, didn't test it, but the main idea is to count the rows in the connecting table.

scope :in_categories, lambda { |*categories|
      joins(:categories).
      where(:awards_categories => { :category_id => categories } ).
      where("(select count(distinct category_id) from awards_categories where category_id in (?)) = ?", categories, categories.size)
    }

并以这种方式使用它:

@awardsall = Award.in_categories(1, 2).order("name ASC") 

@awards_store = Award.in_categories(1).order("name ASC") 

如果你有一个用于 Awards_categories 的模型，那么它看起来会更好:

If you have a model for awards_categories, then it will look better:

scope :in_categories, lambda { |*categories|
      joins(:categories).
      where(:awards_categories => { :category_id => categories } ).
      where("#{AwardCategory.where(:category_id => categories).count("distinct category_id").to_sql}=#{categories.size}")
    }

这篇关于Rails 3、HABTM如何带条件查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！