新手问题 关于多对多关联的一个查询问题！
product 和 category 多对多关联，根据 category 搜索 product! 应该如何写？ 数据库是 mongodb，用 mongoid！
Product
id:1, category_ids:[1,5,6]
id:2, category_ids:[1,3,6]
id:3, category_ids:[2,3,5]
id:4, category_ids:[4,5,6]
现在条件是 [5,6]，应该怎么写才能搜索出 id:1 和 id:4？？？
这个是 java 版本的，楼主可以参考 @Query("select p from Product p inner join p.tags tag where tag IN (:tags) GROUP BY p HAVING COUNT(tag) >= :size") size 就是同时符合的 tag 数量
猜测一下，数据库中有 3 张相关的表，分别为：products，categories 和 product_categories。
楼主试试这个：
select p.id from products as p left join product_categories as pc on (p.id = pc.product_id) where p.category_id in :id_arr
:id_arr 就是指［5，6］这个 id 数组。
Product.all(category_ids: [5, 6])
@Teddy @lionzixuanyuan @Rei 谢谢！
MOPED: 127.0.0.1:27017 COMMAND database=meiyuewuyou_development command={:count=>:products, :query=>{"category_ids"=>{"$all"=>[9, 2, 6]}}} (0.4029ms)
=> #<Product _id: 3, _type: nil, created_at: 2013-04-27 01:37:21 UTC, updated_at: 2013-04-27 05:30:24 UTC, liked_user_ids: [], likes_count: 0, name: "干爽网面", howmany: nil, price: 0, exprice: 0, inprice: 0, size: "", use: nil, feature: "", inventory: 0, sales: 0, cover: 0, selling: true, barcode: "12121121212", special_offer: false, node_id: 1, manufacturer_id: 6, category_ids: ["2", "6", "9"], identifier: "", smell: "">
但是搜索没结果，是不是category_ids有顺序限制？
@Rei 因为是多对多，所以在 product 的表单里用 checkbox，他自动保存为字符
<%= check_box_tag "product[category_ids][]", category.id, @product.categories.map(&:id).include?(category.id) %>
params[:product][:category_ids] = params[:product][:category_ids].map{|i| i.to_i}
logger.info(params[:product][:category_ids])
在 create 转换了为 i，但是保存到数据库还是 s，category_ids: ["5", "8", "3"]
conditions.merge!(:category_ids => params[:category].split("-").map{|i| i.to_s}) unless params[:category].blank?
logger.info(params[:category].split("-")) unless params[:category].blank?
logger.info(params[:category].split("-").map{|i| i.to_s}) unless params[:category].blank?
@products = Product.all(conditions).paginate(:page => params[:page], :per_page => 10)
而在搜索的时候转换，也没用！我用 logger.info 输出，不转换已经是 ["5", "8", "3"] 了，但是执行数据库搜索的时候，就会变成 [5, 8, 3]!
Product.find(5) & Product.find(6)
可利用数组的求交集操作：& 具体的 find 只有自己去实现了，没用过 mongodb。
find(5) => ids: [1, 3, 4]
find(6) => ids: [1, 2, 4]
find(5) & find(6) => [1, 4]
@ChanceDoor {:node_id=>"1", :category_ids=>["9", "3", "6"]}这是 conditions！
这是 product,category_ids: ["6", "9", "3"]
#<Product _id: 3, _type: nil, created_at: 2013-04-27 01:37:21 UTC, updated_at: 2013-04-28 03:38:38 UTC, liked_user_ids: [], likes_count: 0, name: "干爽网面", howmany: nil, price: 0, exprice: 0, inprice: 0, size: "", use: nil, feature: "", inventory: 0, sales: 0, cover: 0, selling: true, barcode: "12121121212", special_offer: false, node_id: 1, manufacturer_id: 6, category_ids: ["6", "9", "3"], identifier: "", smell: "">
@zfjoy520 你误会了！我不是要交集！我的意图是：几个商品，同时拥有 category_ids:["6","9","3"]！现在传入参数 category=6-3-9，然后把他转换成 array，传入 conditions，然后进行搜索！
@zfjoy520 恩，商品可能拥有更多 categories，例如 [3,7,12,6,8,9]! 顾客可能要筛选，[6,3,9]，所以要搜索出 products 中拥有 [6,3,9]categories 的 products，返回给顾客，product 和 category 是多对多的关联
@zfjoy520 @ChanceDoor 我本来也是这种，这样做不能通用，淘宝那些应该也不是这样的。有不同的商品这种方法就不好使了，例如有鞋子和手机，他们的属性没一个相同，所以 product 这个 model 里面，没可能有这么多 xx_id 呢！
@Rei 我看看，我代码如下 product form
<div class="control-group">
<label class="control-label">面质</label>
<div class="controls">
<label class="checkbox inline">
<input id="product_category_ids_" name="product[category_ids][]" type="checkbox" value="2" />
棉柔
</label>
<label class="checkbox inline">
<input checked="checked" id="product_category_ids_" name="product[category_ids][]" type="checkbox" value="3" />
网面
</label>
</div>
</div>
products_controller.rb create
def create
@product = Product.new(params[:product])
if @product.save
session[:admin_return_to] ||= cpanel_products_path
redirect_to session[:admin_return_to], :notice => "保存成功!"
else
render :action => "new", :notice => "保存失败!"
end
end
# encoding: utf-8
class Product
include Mongoid::Document
include Mongoid::Timestamps
include Mongoid::BaseModel
include Mongoid::CounterCache
include Mongoid::Likeable
field :name #产品名称
field :howmany, :type => Integer#内含多少片
field :price, :type => Float, :default => 0 #销售价格
field :exprice, :type => Float, :default => 0 #超市价格
field :inprice, :type => Float, :default => 0 #入货价格
field :size #规格
field :use #用途
field :feature #产品特点
field :inventory, :type => Integer, :default => 0 #库存量
field :sales, :type => Integer, :default => 0 #销售量
field :cover, :type => Integer, :default => 0 #封面
field :selling, :type => Boolean, :default => true #在售
field :barcode, :default => "" #条形码
field :special_offer, :type => Boolean, :default => false
belongs_to :node, :inverse_of => :products
belongs_to :manufacturer, :inverse_of => :products
has_many :line_items
has_many :pictures, :as => :imageable, :dependent => :destroy
has_and_belongs_to_many :categories
attr_accessible :name, :howmany, :price, :exprice, :inprice, :size, :smell, :use, :feature, :sales, :manufacturer_id, :cover, :identifier, :barcode, :selling, :inventory, :category_ids,:special_offer, :node_id
validates_presence_of :manufacturer_id, :name
validates_uniqueness_of :barcode, :name
index :inventory => 1
index :likes_count => 1
scope :low_inventory, where(:inventory.lt => 5)
scope :high_likes, desc(:likes_count, :_id)
scope :popular, where(:likes_count.gt => 5)
scope :sell, where(:selling => true)
scope :special_offering, where(:special_offer => true)
def format_selling
self.selling ? "上架" : "下架"
end
end
class Category
include Mongoid::Document
include Mongoid::Timestamps
include Mongoid::BaseModel
field :name
field :sort, :type => Integer, :default => 0
belongs_to :node
belongs_to :parent, :class_name => "Category"
has_many :children, :class_name => "Category", :foreign_key => "parent_id", :dependent => :destroy
has_and_belongs_to_many :products
scope :roots, where(:parent_id => nil)
end
2.0.0-p0 :003 > p.update_attributes(:category_ids => [4,5,10])
MOPED: 127.0.0.1:27017 COMMAND database=admin command={:ismaster=>1} (0.3510ms)
MOPED: 127.0.0.1:27017 UPDATE database=meiyuewuyou_development collection=products selector={"_id"=>3} update={"$set"=>{"category_ids"=>[4, 5, 10], "updated_at"=>2013-04-28 11:48:22 UTC}} flags=[] (0.1547ms)
MOPED: 127.0.0.1:27017 UPDATE database=meiyuewuyou_development collection=categories selector={"$and"=>[{"_id"=>{"$in"=>[4, 5, 10]}}]} update={"$addToSet"=>{"product_ids"=>3}} flags=[:multi] (0.0887ms)
MOPED: 127.0.0.1:27017 UPDATE database=meiyuewuyou_development collection=categories selector={"$and"=>[{"_id"=>{"$in"=>[6, 9, 3]}}]} update={"$pull"=>{"product_ids"=>3}} flags=[:multi] (0.0861ms)
=> true
2.0.0-p0 :005 > p
=> #<Product _id: 3, _type: nil, created_at: 2013-04-27 01:37:21 UTC, updated_at: 2013-04-28 11:48:22 UTC, liked_user_ids: [], likes_count: 0, name: "干爽网面", howmany: nil, price: 0, exprice: 0, inprice: 0, size: "", use: nil, feature: "", inventory: 0, sales: 0, cover: 0, selling: true, barcode: "12121121212", special_offer: false, node_id: 1, manufacturer_id: 6, category_ids: [4, 5, 10], identifier: "", smell: "">
category_ids: [4, 5, 10]
那就是 action 里面要转一下类型了。
@product = Product.new(params[:product].merge(:category_ids => params[:product][:category_ids].map(&:to_i))