Odoo Help

2

XMLRPC : complex search (like join in SQL)

By
cd
on 10/13/13, 5:10 AM 3,568 views

Can you confirm that :

-it's not possible to do a search with XMLRPC on 2 tables, like a JOIN in SQL (without creating a new module) ?

-i want to look for the account.invoice.line that contains a certain product, with account.invoice of type "out". In SQL it's a simple join, but with the search method with XMLRPC I can't see how i can do that.

-furthermore, why in standard there isn't a special XMLRPC method that would process any SQL query (SELECT only) ? It would simplify the work.

0
Brett Lehrer
On 10/22/13, 12:31 AM

Technically speaking you could just do a cr.execute() call to get exactly what you want. That's what cr is in nearly every python function, the database cursor. There's no XMLRPC command for joins though. Check the psycopg2 documentation for more info on how to use that properly. http://initd.org/psycopg/docs/usage.html

cr.execute("""SELECT account_invoice_line.id
    FROM account_invoice_line LEFT JOIN (
    SELECT id FROM account_invoice WHERE type='out_invoice'
    ) AS foo ON foo.id=order_line_id
    WHERE product_id=%s""", str(my_product_id))
results = cr.fetchall()
invoice_line_ids = []
for res_id in results:
    invoice_line_ids.append(res_id[0])

Note that the result will be a list of lists of a single integer, so that's why I included a for loop to convert it to a single list of IDs. Also, converting the integer to a string first is proper psycopg2 syntax.

About This Community

This platform 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.

Register

Odoo Training Center

Access to our E-learning platform and experience all Odoo Apps through learning videos, exercises and Quizz.

Test it now

Question tools

0 follower(s)

Stats

Asked: 10/13/13, 5:10 AM
Seen: 3568 times
Last updated: 3/16/15, 8:10 AM