多对多关系过滤器 [英] Many-to-many relation filter

问题描述

我需要使用与另一个表有许多关系的类别表过滤我的查询。可以用many2many关系过滤查询吗？

表 res_partner 有许多与表相关的许多字段 category_id res_partner_category.res_partner ，或者让我们说合作伙伴可以有很多类别。我需要的是过滤 res_partners 表，其中有商业或零售类别。如果没有这些类别，则不应显示。

另外还有一个 res_partner 它是 category_value_ids ，并且 one2many 与关系res_partners_category_value ：

res_partner 具有以下与关系的字段：

• category_id to res_partner_category （many2many）


• category_value_ids to res_partner_category_value （one2many）


• 名称（char）

res_partner_category 具有以下与关系的字段：

• partner_ids to res_partner （many2many）


• name （char）

res_partner_category_value 具有以下与关系的字段：

• category_group_id to res_partner_category （many2one）


• category_id to res_partner_category （many2one）


• object_id to res_partner （many2one）






但是如果我尝试在SQL查询中使用 res_partner_category_value 表，我得到的错误，我无法在查询中使用它。





所以例如，如果有4个合作伙伴有这些类别：






• 第一：categ1，categ2，business


• 第二：零售


• 第三：零售业务

< categ1，categ2





查询应返回第一，第二和第三个合作伙伴。

一个人告诉我不是可能像这样与许多关系过滤。所以我不知道真的不可能或只是复杂吗？





编辑：

我发现一个更多的表称为 res_partner_category_rel 。我没看到它，因为在Openerp管理界面中，您可以看到数据库的所有对象，该表未显示。您只能通过数据库直接查看。
所以我被这个丢失表感到困惑：





res_partner_category_rel：






• partner_id （many2one）


• category_id （many2one）





解决方案

这是测试用例应该提供：

  CREATE TABLE partner（
 partner_id serial PRIMARY KEY 
，partner text 
）; 
 INSERT INTO伙伴（合作伙伴）VALUES 
（'partner1'）
，（'partner2'）
，（'partner3'）
，（'partner4'） 
; 
 CREATE TABLE类别（
 category_id serial PRIMARY KEY 
，类别文本
）; 
 INSERT INTO类别（类别）VALUES 
（'categ1'）
，（'categ2'）
，（'business'）
，（'零售' ; 
 
 CREATE TABLE partner_category（
 partner_id int REFERENCES partner（partner_id）
，category_id int REFERENCES类别（category_id）
，CONSTRAINT cat_pk PRIMARY KEY（partner_id，category_id）
）; 
 INSERT INTO partner_category（partner_id，category_id）VALUES 
（1,1），（1,2），（1,3）
，（2,4）
，（ 3,3），（3,4）
，（4,1），（4,2）; 
  

这是你之后的查询（许多可能的变体）：

  SELECT p。* 
 FROM partner p 
 WHERE EXISTS （SELECT * FROM partner_category pc 
 WHERE pc.partner_id = p.partner_id AND pc.category_id = 3）
 OR EXISTS（SELECT * FROM partner_category pc 
 WHERE pc.partner_id = p.partner_id AND pc.category_id = 4）
 ORDER BY p.partner_id; 
  

SQL Fiddle。





这里的关键词是关系划分。我们组织了一大堆查询来处理这个相关问题下的这类问题：






• 如何以多对多关系过滤SQL结果





I need to filter my query with categories table which has many2many relation with another table. Is it possible to filter query with many2many relation?

Table res_partner has many2many field category_id relating to table res_partner_category.res_partner, or let's just say partners can have many categories. What I need is to filter res_partners table where it has category named 'business' or 'retail'. If it doesn't have any of these categories, it should not be shown.

Also there is another field in res_partner which is category_value_ids and has one2many relation with res_partners_category_value:

res_partner has following fields with relations:

• category_id to res_partner_category (many2many)
• category_value_ids to res_partner_category_value (one2many)
• name (char)

res_partner_category has following fields with relations:

• partner_ids to res_partner (many2many)
• name (char)

res_partner_category_value has following fields with relations:

• category_group_id to res_partner_category (many2one)
• category_id to res_partner_category (many2one)
• object_id tores_partner (many2one)

But if I try to use res_partner_category_value table in SQL query I get error that I can't use it in query.

So for example, if there are 4 partners with these categories:

• first: categ1, categ2, business
• second: retail
• third: retail, business
• fourth: categ1, categ2

The query should return first, second and third partners.
One person told me it's not possible to filter like this with many2many relation. So I wonder is it really not possible or just complicated?

EDIT:
I found one more table called res_partner_category_rel. I didn't see it, because in Openerp administration interface, where you can see all objects of database, that table is not shown. You can only see it directly through database. So I was confused by this "missing" table:

res_partner_category_rel:

• partner_id (many2one)
• category_id (many2one)

解决方案

This is the test case you should have provided:

CREATE TABLE partner (
  partner_id serial PRIMARY KEY
, partner    text
);
INSERT INTO partner (partner) VALUES 
  ('partner1')
, ('partner2')
, ('partner3')
, ('partner4')
;    
CREATE TABLE category (
  category_id serial PRIMARY KEY
, category    text
);
INSERT INTO category (category) VALUES 
  ('categ1')
 ,('categ2')
 ,('business')
 ,('retail');

CREATE TABLE partner_category (
  partner_id  int REFERENCES partner(partner_id)
, category_id int REFERENCES category(category_id)
, CONSTRAINT cat_pk PRIMARY KEY (partner_id, category_id)
);
INSERT INTO partner_category (partner_id, category_id) VALUES 
   (1,1), (1,2), (1,3)
  ,(2,4)
  ,(3,3), (3,4)
  ,(4,1), (4,2);

And this is the query you are after (one of many possible variants):

SELECT p.*
FROM   partner p
WHERE  EXISTS (SELECT * FROM partner_category pc
               WHERE  pc.partner_id = p.partner_id AND pc.category_id = 3)
OR     EXISTS (SELECT * FROM partner_category pc
               WHERE  pc.partner_id = p.partner_id AND pc.category_id = 4)
ORDER  BY p.partner_id;

SQL Fiddle.

The key word here is relational division. We have assembled a whole arsenal of queries to deal with this class of problems under this related question:

• How to filter SQL results in a has-many-through relation

这篇关于多对多关系过滤器的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！