0

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)
user4157124
  • 2,809
  • 13
  • 27
  • 42
Andrius
  • 19,658
  • 37
  • 143
  • 243
  • 1
    I'm confused by the fields in each table. For a many-to-many relationship between tables A and B, neither A nor B should have foreign key fields that point to records in the other table. Instead there should be a separate table, C, that contains pairs of foreign key fields (1 for A, 1 for B). But from your description, `res_partner` contains a `category_id` field... Why? – j_random_hacker Nov 06 '12 at 15:55
  • 1
    @ j_random_hacker That's an OpenERp feature: you can define a many2many "field" from A to B, and the relation table C is automatically (automagically?) managed by the ORM. – Daniel Reis Nov 06 '12 at 18:08

2 Answers2

5

Setup

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);

Solution

One way:

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;

Another:

SELECT p.*
FROM        (SELECT partner_id FROM partner_category WHERE category_id = 3) pc1
FULL   JOIN (SELECT partner_id FROM partner_category WHERE category_id = 4) pc2 USING (partner_id)
JOIN   partner p USING (partner_id)
ORDER  BY p.partner_id;

fiddle
Old sqlfiddle

The second one assumes unique (partner_id, category_id) in partner_category.

Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
  • Well actually there is no such table as category and partner_category do not have field `partner_id`. Your query looks good, but too bad I can't use it, because tables relations are a bit different in openerp... It has relations like I written. – Andrius Nov 07 '12 at 06:57
  • @oerp: If your tables are structurally different, then I suggest you update the question with a setup like I demonstrated. So people trying to help have something to work with. However, as long as we are, in fact, dealing with an m:n relationship, my example should hold. – Erwin Brandstetter Nov 07 '12 at 07:06
  • Also at 'and category_id=3', shouldn't it be 'and pc.category_id=3'? – Andrius Nov 07 '12 at 08:35
  • Thanks for the answer, I really solved now, when I found one table that didn't show up:) – Andrius Nov 07 '12 at 09:30
  • @oerp: Well, yes, `category_id = 3`, should be `pc.category_id = 3`. It works either way - without table-qualification, the scope default to `pc` within the subquery. But for readability I ammended it. Cool that it works for you now. :) – Erwin Brandstetter Nov 07 '12 at 10:44
2

As you already noticed, the many2one category_id is not represented in the database as a table field, but as a table relating Partners and Categories.

The SQL you need could look like this:

SELECT p.* 
FROM res_partner p
  INNER JOIN res_partner_category_rel rel ON p.id = rel.partner_id
    INNER JOIN res_partner_category c ON rel.category_id = c.id
WHERE c.id in (3,4)

If you want to do the filter in the python object, the usual searchcall should work:

list_ids = partner_model.search(cr, uid, [('category_id', 'in', [3,4])])

As a bonus, since Categories are organized in a tree, you could get those categories and all their children using:

list_ids = partner_model.search(cr, uid, [('category_id', 'child of', [3,4])])
Daniel Reis
  • 12,944
  • 6
  • 43
  • 71