CODEWITHSUNDEEP
sqlstorm-orm
Georg Schölly
Georg Schölly

Reputation: 126165

How to add a large array to an SQL query?

This is my storm code:

items = store.find((Delivery, Product, Sum(Delivery.quantity)),
                    Delivery.id.is_in(order.id for order in self.orders),
                    Product.id == Delivery.product_id) \
                    .group_by(Delivery.date, Product.id, Delivery.discount) \
                    .order_by(Delivery.date, Product.name, Delivery.discount)

This translates to about the following SQL:

   SELECT deliveries.id, products.id, SUM(deliveries.quantity)
   FROM deliveries, products
   WHERE products.id = deliveries.id AND
->       deliveries.id IN (1,10,5,24,122, ...)
   GROUP BY deliveries.date, product.id, delivery.discount
   ORDER BY deliveries.date, product.id, delivery.discount

If self.orders is too long, a Too many SQL variables exception is raised.

Is there an alternative way to achieve this?

My only idea is to create a query for every single order and combine them manually using a dictionary.

Upvotes: 0

Views: 265

Answers (2)

Unsliced
Unsliced

Reputation: 10552

  • Add it to a temporary table/table variable and link to it.
  • Depending on your version of SQL, you might be able to use an XML object

Upvotes: 1

hsz
hsz

Reputation: 152284

Maybe just split this targe array into smaller ones ? Something around 1/3 of its size and then merge it in script side.

Upvotes: 0

Related Questions