Search : [(M2M/O2M ,operator, False/[])]

[forstera]

Hello all,
I need to provide accounting reports for my boss but I get the following problem with account.move.lines :
I need to filter the records to get only the ones between 2 dates. The fields dates in the filter section don't filter the date of the 'availability' of the account.move.line records but the date of the creation of the lines...

I join 2 printscreen of the problem...

Thanks
Arnaud

[Dhruti Shastri(OpenERP) (dhs-openerp)]

Hello Arnaud,

Thanks for reporting such a critical issue.

This is an issue when search is performed on a field which is of type related. In your case in particular,when there are no matches found for field date,it simply returns all records.

Please apply the patch attached herewith and let us know about the outcome.

Thanks

[Anup(SerpentCS) (anup-serpent)]

Hello Arnaud,

      I have found an optimized solution of it. I have attached the optimized patch. Would you please have a look at it and give your views.

Thanks

[Jay Vora (Serpent Consulting Services) (jayvora)]

Hi Experts,

We are actually in the process to decide the behavior of search on O2M and M2M for blank ids.

Search of blank ids on O2M and M2M might be treated by one of the ways as said below OR a CONCRETE way which can ensure a perfect result.

AIM 1 : Get all partners with no address
            self.pool.get('res.partner').search(cr, uid, [('address','in',[])])
          Result : I get partners whose address is null.
          Conclusion : Result is RIGHT.

AIM 2 : Get all partners with city='mycity' (search on related)
          First domain goes as [('city','ilike','mycity')]
          It is converted to a DB compatible query domain as city is a field of address.
         there are no addresses which match 'mycity' anyhow.
         self.pool.get('res.partner').search(cr, uid, [('address','in',[])])
         Result : I get partners whose address is null.
         Conclusion : Result is WRONG.
         Expected result : no partners.( Logically I searched as partners with city 'mycity')

We would like to have views from you where we can end up best solutions for the 2 cases mentioned above.

Thanks.

[Russell Briggs (russell-briggs)]

AIM 1: Searching for empty lists

I don't think it makes sense to search for [('address', 'in', [])] to get all items with no address. 'In' statements are used to search a list of values for a matching item. If the list of items is empty, then no items should be found.

To search for an empty list, logically I would use [('address', '=', [])]. Is that possible to implement?

AIM 2: As you mentioned this behaviour is definitely wrong and should be fixed (provided a simple fix in bug 655889)

[jftempo (jfb-tempo-consulting)]

> To search for an empty list, logically I would use [('address', '=', [])].

I'm not agree with you.
Get all partners with no address: [('address', '=', False)]

[Borja López Soilán (NeoPolus) (borjals)]

@Russell: that (alone) won't work as it can't be used from the user interface!

@jftempo False does not always work (has problems with translatable fields and related fields), but None seems to work:

>>> self.pool.get('account.invoice').search(cr, uid, [('comment', '=', None)])
[]

>>> self.pool.get('account.invoice').search(cr, uid, [('comment', '=', False)])
[2010-10-08 18:39:09,404] ERROR:db.cursor:Programming error: operator does not exist: text = boolean
LINE 1: ... = E'en_US' AND type = E'model' AND value = false) U...

[Borja López Soilán (NeoPolus) (borjals)]

Jay, I think that on the 6.0 user interface the next unary operators should be added to the user-defined filters: "is null", "is zero / empty".

Why?

Because currently, not only there is no way to define a search like "all the invoices without total amount" or "all the partners without code" (looking for NULL values), but also is not possible to search for "all the invoices with a total amount of 0" or "all the partners with an empty code"!

Explaining it a bit this "new" operators (new facade for the existent operators):

"is null" --------------------------

Case 1 (number)

The user wants:
    "all the invoices with a null (not-set-yet) total amount"
The user searchs:
    "Total" + "is null" + "" (empty value) on the invoices search view.
The server does:
    self.pool.get('account.invoice').search(cr, uid, [('amount_total', '=', None)])
Postgres does:
    select account_invoice.id from "account_invoice" where account_invoice.amount_total IS NULL order by number

Case 2 (string)

The user wants:
    "all the invoices with an null (not-yet-set) description"
The user searchs:
    "Description" + "is null" + "" on the invoices search view.
The server does:
    self.pool.get('account.invoice').search(cr, uid, [('name', '=', None)])
Postgres does:
    select account_invoice.id from "account_invoice" where account_invoice.name IS NULL order by number

"is zero / empty" ---------------------------

Case 1 (number)

The user wants:
    "all the invoices with a total amount of 0"
The user searchs:
    "Total" + "is zero / empty" + "" on the invoices search view.
The server does:
    self.pool.get('account.invoice').search(cr, uid, [('amount_total', '=', 0)])
Postgres does:
    select account_invoice.id from "account_invoice" where (account_invoice.amount_total = E'0.00') order by number

Case 2 (string)

The user wants:
    "all the invoices with an empty comment" [remember that empty <> null]
The user searchs:
    "Total" + "is zero / empty" + "" on the invoices search view.
The server does:
    self.pool.get('account.invoice').search(cr, uid, [('name', '=', '')])
Postgres does:
    select account_invoice.id from "account_invoice" where (account_invoice.name = E'') order by number

[Borja López Soilán (NeoPolus) (borjals)]

About AIM 2, I also think it shouldn't return any partner: The user will usually want an "INNER JOIN"-like behaviour (only the partners whose address is the wanted one), not the current (buggy) behavior.

[Anup(SerpentCS) (anup-serpent)]

Hello,

     Our R&D Teams are focused on the latest OpenERP version, and this issue does not affect it. Our policy is to keep the changes applied on stable branches to a minimum, in order to limit the regression risks for customers that are in production. This means that bugs reported on Launchpad are fixed in the trunk branch only by default, even if they were reported against other stable versions.

     We stand of course ready to backport the change to stable releases if it has an impact on any customer. In this case please report it to our maintenance team via the OpenERP Publisher's Warranty. They will quickly help solve the issue and backport the fix if needed.

Thank you for your understanding!

[xrg (xrg)]

Fix is under scientific laboratory experiments ;)

[Olivier Dony (Odoo) (odo-openerp)]

The fix for the special domain expression ['x2m', 'in', [ ]] (it must return empty results), courtesy of P.Christeas was merged in revision 3061 rev-id: <email address hidden>
This should take care of the incorrect behaviors described in this bug description and comments.

The next revision after the fix also includes yaml tests that cover it.