select query into array of ID's

[Drazen D. Babic]

Hi Andrew and others,

this might be interesting for someone, how to use an array of ID's from the result of first query? For example:

    res, inventory = [], product = [], prod = [], ''

    product = item.task.products.copy()
    product.open(fields=['id'],
        group_by=['id'], order_by=['id'])
    for i in product:
        prod += i.id.display_text + ','
    print(prod)
    order_details = item.task.order_details.copy()
    order_details.set_fields('product_id', 'status_id', 'quantity')
    order_details.set_where(product_id__in=prod);
    order_details.open(fields=['product_id', 'quantity', 'status_id'],
        funcs={'quantity': 'sum'},
        group_by=['product_id'], order_by=['product_id'])

This will produce SQL with correct ID's, but on second query it is not correct:

SELECT "Products"."ID" FROM "Products" AS "Products" GROUP BY "Products"."ID" ORDER BY "Products"."ID"
1,3,4,5,6,7,8,14,17,19,20,21,34,40,41,43,48,51,52,56,57,65,66,72,74,77,80,81,82,83,85,86,87,88,89,90,91,92,93,94,95,96,97,98,99,

SELECT "Order_Details"."Product_ID", SUM("Order_Details"."Quantity") AS "Quantity", "Order_Details"."Status_ID", Products_218."Product_Name" AS Product_ID_LOOKUP, Order_Details_Status_222."Status_Name" AS Status_ID_LOOKUP FROM "Order_Details" AS "Order_Details" OUTER LEFT JOIN "Products" AS Products_218 ON "Order_Details"."Product_ID" = Products_218."ID" OUTER LEFT JOIN "Order_Details_Status" AS Order_Details_Status_222 ON "Order_Details"."Status_ID" = Order_Details_Status_222."ID_ROWID" WHERE "Order_Details"."Product_ID" IN (1, ',', 3, ',', 4, ',', 5, ',', 6, ',', 7, ',', 8, ',', 1, 4, ',', 1, 7, ',', 1, 9, ',', 2, 0, ',', 2, 1, ',', 3, 4, ',', 4, 0, ',', 4, 1, ',', 4, 3, ',', 4, 8, ',', 5, 1, ',', 5, 2, ',', 5, 6, ',', 5, 7, ',', 6, 5, ',', 6, 6, ',', 7, 2, ',', 7, 4, ',', 7, 7, ',', 8, 0, ',', 8, 1, ',', 8, 2, ',', 8, 3, ',', 8, 5, ',', 8, 6, ',', 8, 7, ',', 8, 8, ',', 8, 9, ',', 9, 0, ',', 9, 1, ',', 9, 2, ',', 9, 3, ',', 9, 4, ',', 9, 5, ',', 9, 6, ',', 9, 7, ',', 9, 8, ',', 9, 9, ',') GROUP BY Products_218."Product_Name", "Order_Details"."Product_ID" ORDER BY Products_218."Product_Name"

The set_where for second query would fix the need for Python loop where I iterate the rows for ID's.

Thanks

[Drazen D. Babic]

It is actually a LIST, not an array. So when I do this:

    for i in product:
        prod = []
        prod.append(i.id.value)
        print(prod)

This will print:

[1]
[3]
[4]
.
.
[99]

It is not creating it as [1,3,4...99] so the SELECT will be:

SELECT "Order_Details"."Product_ID", SUM("Order_Details"."Quantity") AS "Quantity", "Order_Details"."Status_ID", Products_218."Product_Name" AS Product_ID_LOOKUP, Order_Details_Status_222."Status_Name" AS Status_ID_LOOKUP FROM "Order_Details" AS "Order_Details" OUTER LEFT JOIN "Products" AS Products_218 ON "Order_Details"."Product_ID" = Products_218."ID" OUTER LEFT JOIN "Order_Details_Status" AS Order_Details_Status_222 ON "Order_Details"."Status_ID" = Order_Details_Status_222."ID_ROWID" WHERE "Order_Details"."Product_ID" IN (99) GROUP BY Products_218."Product_Name", "Order_Details"."Product_ID" ORDER BY Products_218."Product_Name"

I do not understand why the list is not populated with append. Anyone knows?

This basically means using ONE query and the result of this query in the second query. And so on.  

Cheers

[Fabio Lenzarini]

Hi Drazen

try this.
    prod = []

    for i in product:
        prod.append(i.id.value)
    print(prod)

in your code, you reset "prod" each "i"

ciao

Fabio