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.

2

XMLRPC : complex search (like join in SQL)

By
cd
on 10/13/13, 5:10 AM 2,816 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

--Brett Lehrer--
1252
| 5 4 8
Milwaukee, United States
--Brett Lehrer--
Director of IT and open source enthusiast.
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.

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

0 follower(s)

Stats

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