Odoo Help

Welcome!

This community is for beginners and experts willing to share their Odoo knowledge. It's not a forum to discuss ideas, but a knowledge base of questions and their answers.

1

How to perform a relational division in a domain expression? left[] "is contained in" right[]

By
David Arnold
on 6/27/14, 4:07 AM 1,103 views

Due to the fact, that in the standard ORM expressions.py there is no operator for a relational division, I wonder how I can acomplish this or with a core hack, or with a custom code on a higher level. Case many2many (actually the one 2 many case works as - if I understand it right -  it can be solved without a true division)

Please do not suggest the "in" operator as it is a chained in statment in the form (refer to expression.py arround line 967):

The intermediary table has the folowing form:
rel_id1 , rel_table, rel_id2

Then a first SQL executes:

SELECT rel_id1 FROM rel_table WHERE rel_id2 IN right[]

This is stored in a list2[] with then is passed into a substitution leaf like so: ('id', 'in', list2[])

This is then transforemd into something so that, it returns true whenever a a single value of a many2many field is within right[].

 

What is needed is a true relational division with an remainder, if cardinality is not equal. So that we can say in plain english semantics:

left[] "is contained in" right[] instead of "at least one of the items in" left[] "is contained in" right[] <=> left[] "in" right[]

Thank you!

Extremely interessting question! Do you want to left[] to be contained in right[] in the exact order? What type of lists do you want to compare?

René Schuster
on 6/27/14, 4:33 AM

Servus! The order is not important in my case, however curiosity has the reputation of killing cats... In my search on relational division I however did not find any comments on ordering at first glance. I imagine however this is an even more cmplex task, as ordering information (if it should be significant added value) is not in the id itself, but in a sequence field. The Stackoverflow-Rerference post on this topic is: http://stackoverflow.com/questions/7364969/how-to-filter-sql-results-in-a-has-many-through-relation

David Arnold
on 6/27/14, 4:51 AM

And should left[] be containted consecutively in righ[]?

René Schuster
on 6/27/14, 4:52 AM

Type of List: Its a construction of a large domain statment for a .search() on some tax rules that aply in a given context. To be flexible, the tax properties have kind of meta classifieres and are applied (context aware, thanks to meta classifiers) onto several objects (product, company, partner) as many2many attributes. On runtime the attributes_all_ids is constructed (reverse context aware) to constitute the right-side list. If, now, the attributes (m2m) specified in a rule (left list) are a subset or equal to the right list, this domain statment should return true. In other words, this rule applies. The point is about the m2 side which in combination with the 2m side makes a relational division (with conditional remainder) required. m2m is used to maintain flexiblity end be able to reduce complexity through multidimensionality of attirbute-sets thanks to metha classifieres...

David Arnold
on 6/27/14, 4:59 AM

With this I negate your third question as well. Consecutively is not important. In subset theroy a subset or equal set should return true.

David Arnold
on 6/27/14, 5:02 AM

Additional referenc on the community mailing list: https://lists.launchpad.net/openerp-community/msg06207.html Actual Non-Working Code: https://github.com/odoo-colombia/odoo_advanced_tax_engine/blob/master/account_fiscal_allocation_rule/models/fiscal_allocation_rule.py#L195

David Arnold
on 6/27/14, 5:08 AM

Rene, your reasoning is quite interesting. It sugest's to make use of this weirdness of in. It assumes that "! not in" (double negation) "in", the rational being, that "!" and "not" would not have the same effect. (As "in" does not what we would expect from english semantics I'm proned to believe that this is not a bad guess) The point might be the "not" rahter then the "!" part: "in" results true if there is a signle hit. So the problem is, what is "not"? Does "not" mean if there is not a "single hit" (eg. a duble hit)? I suppose "not" is not "not" here. And the semantics might be confused, as in the code, one can see, that the many2many case hasn't been thought trough thouroughly... On the other hand, I've seen a method in expressions.py which renders "!" to "not", if my above assumption is true (which would make your advise work in the first place) this would be a terrible violation of logical semantics. On the other henad, this method would make your advise not work, which would have worked in the first place.. Please correct me, I like those discussions ;) But they are on the edge of my usual brain usage.. ;)

David Arnold
on 6/30/14, 3:19 PM

"! not in" (double negation) "in" Here was an UNEQUAL SING which was absorbed by the page renderer like this: "! not in" (double negation) UNEQUAL SING "in"

David Arnold
on 6/30/14, 3:20 PM
0
David Arnold
On 6/30/14, 3:02 PM

GRAEME GELLATLY posted an answer on the communtiy maling list, which is a python workaround, so no raltional division is needed on the database level. It will be copied:

Sorry, I just can't understand why adding an ORM search operator is the right solution here given the difficulty of the underlying SQL and how simple it is to do in python/using ORM.  Especially since this is hardly a major issue faced by lots of people.  Then again I am more functional than technical.  Why can't you just omit that part of the domain and test after using python's set operations?  All the below is untested pseudocode with no exception catching but should give the idea.  Again - not a techie, just how a functional person with a little bit of python would write it.  I wouldn't mind betting this would be faster as well.
 

e.g. 

In your function

right_set_of_ids = [n,n,n,...]
some_list_of_ids = self.search(...args)

# If you only want True results as a list - dict version

some_list_of_ids = [record_id for record_id, value in self.x2many_issubset(cr, uid, some_list_of_ids, 'x2m_field_name', right_set_of_ids, context=context).iteritems() if value]

# If you only want True results as a list - tuple version

some_list_of_ids = [record_id for record_id, value in self.x2many_issubset(cr, uid, some_list_of_ids, 'x2m_field_name', right_set_of_ids, context=context) if value]
 

then have

dict version - as everyone loves dicts

def x2many_issubset(self, cr, uid, ids, field_name, right_ids, context=None):

    res = {}
    right_ids = set(right_ids)

    for record in self.browse(cr, uid, ids, context=context):

        left_ids = set([x2m.id for x2m in record[field_name]])

        res[record.id] = left_ids.issubset(right_ids)

    return res
 

tuple version

def x2many_issubset(self, cr, uid, ids, field_name, right_ids, context=None):

    res = []
    right_ids = set(right_ids)

    for record in self.browse(cr, uid, ids, context=context):

        left_ids = set([x2m.id for x2m in record[field_name]])

        res.append((record.id, left_ids.issubset(right_ids))

    return res

 

Then either assign the function at ORM level or within the object model itself
 

e.g 

openerp.osv.orm.BaseModel.x2many_issubset = x2many_issubset

or just using normal _inherit(s) mechanism for object models.

 

extend it any way you want, you could add a top level function which takes an operator for example and dynamically calls the correct lower set operation, but that one function covers the use case as I understand.  Should be quick, but one-liner versions below which for large lists of ids should be quite a bit quicker, tuple version quickest.
 

dict version

v2.6
def x2many_issubset(self, cr, uid, ids, field_name, right_ids, context=None):

    right_ids = set(right_ids)

    return dict([(rec.id, set([x2m.id for x2m in rec[field_name]]).issubset(right_ids) for rec in self.browse(cr, uid, ids, context=context)])

v2.7
def x2many_issubset(self, cr, uid, ids, field_name, right_ids, context=None):
    right_ids = set(right_ids)
    return {(rec.id, set([x2m.id for x2m in rec[field_name]]).issubset(right_ids) for rec in self.browse(cr, uid, ids, context=context)}
 

tuple version
def x2many_issubset(self, cr, uid, ids, field_name, right_ids, context=None):
    right_ids = set(right_ids)
    return [(rec.id, set([x2m.id for x2m in rec[field_name]]).issubset(right_ids) for rec in self.browse(cr, uid, ids, context=context)]

1

René Schuster

--René Schuster--
1560
| 5 5 8
Weinheim, Germany
--René Schuster--

Challenge Everything!

René Schuster
On 6/30/14, 6:02 AM

As far as I understand, you are looking for something like an 'all in' operator.

Maybe I'm confused but this should be achievable by using:

('!', 'left[]', 'not in', 'right[]')

Line of thoughts:

  • 'in' performs elementwise test if left[] elements are in right[], and returns true if any element is in right[]:
    left[1] in right[] OR ... OR  left[n] in right[]  => test_1 or test_2 or ... or test_n
  • what you want is a similiar behavior, but it should only return true if all elements are in right[]:
    left[1] in right[] AND ... AND  left[n] in right[]  => test_1 and test_2 and ... and test_n
  • Unfortunately the 'in' operators are always 'or-connected'. So how to convert logical and to logical or?
    Using De Morgans Law: (test_1 and test_2) = not(not(test_1) or not(test_))

Hope it helps a little.

EDIT:

Unfortunately the 'in' operators are always 'or-connected'.

This might not be true....

Maybe ('left[]', 'not in', 'right[]') is the same as (''!', 'left[]', 'in', 'right[]')

In that case, my solution would fail.

 

Regards.

 

Your Answer

Please try to give a substantial answer. If you wanted to comment on the question or answer, just use the commenting tool. Please remember that you can always revise your answers - no need to answer the same question twice. Also, please don't forget to vote - it really helps to select the best questions and answers!

About This Community

This community is for professionals and enthusiasts of our products and services. Read Guidelines

Question tools

2 follower(s)

Stats

Asked: 6/27/14, 4:07 AM
Seen: 1103 times
Last updated: 3/16/15, 8:10 AM