How to express a seemingly straightforward upate in the ORM?

[Benjamin Scherrey]

I've got an application that needs to frequently update a boolean value (ChannelItem.channel_stocks) against a temporary table that gets created and lives just long enough for this transaction. The table is simply a list of alpha-num skus and created like this:

cursor.execute("create temporary table skus (sku varchar);")  # Postgres only

cursor.execute("insert into skus (sku) values %s;" % skus)

This temporary table will hold, at various times, anywhere from 300 to 300,000 items so my preference is a 'join' and not an 'in' filter as that's not likely to be performant at the higher end of that scale.

The ChannelItem model relates to the Partner, Channel, and Item models and is filtered for just the specific Partner/Channel pair that we care about via a for_partner_channel() filter. What I need is to perform the update for the 'ChannelItem.channel_stocks' value only if the related 'Item.sku' is one of the 'sku' in temporary table 'skus'. The following is the cleanest generating SQL I can come up with via the ORM but I can't figure out how to do a join/limit to only those items that are present in the temporary table:

ChannelItem.objects().for_partner_channel(mp,mc).select_related('item').select_related('partner').select_related('channel').extra(tables='skus').update(channel_stocks=True)

It seemed that the .extra(join=...) proposal that has been rejected is the obvious solution. Since that's not an option, what is the correct way to do this?

This seems to me to be a very common kind of operation that one might encounter so I'm surprised it's so difficult (or perhaps less surprised that I just can't figure it out) to represent this operation in the Django ORM. Appreciate any advice otherwise I have to go full raw with this which I'd really prefer not to do.

BTW - if I leave out any of those select_related chains from the above request my number of SQL requests goes up from 1 to hundreds.

thanx,

  -- Ben

--
Chief Systems Architect Proteus Technologies

Chief Fan Biggest Fan Productions

Personal blog where I am not your demographic.

This email intended solely for those who have received it. If you have received this email by accident - well lucky you!!

[Benjamin Scherrey]

I should assume then that this is something that the existing Django ORM cannot model? 

-- Ben

[Erik Cederstrand]

Den 12/09/2014 kl. 20.19 skrev Benjamin Scherrey <prote...@gmail.com>:

> I should assume then that this is something that the existing Django ORM cannot model?

It's a bit hard for me to grok your initial mail, but maybe you can use the new Prefetch() class in Django 1.7 to filter your query using joins instead of 'IN'?

https://docs.djangoproject.com/en/dev/ref/models/queries/#django.db.models.Prefetch

Erik