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 <http://proteus-tech.com>
Chief Fan Biggest Fan Productions <http://biggestfan.net>
Personal blog where I am not your demographic
<http://notyourdemographic.com>.
This email intended solely for those who have received it. If you have
received this email by accident - well lucky you!!
--
You received this message because you are subscribed to the Google Groups
"Django users" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to django-users+unsubscr...@googlegroups.com.
To post to this group, send email to django-users@googlegroups.com.
Visit this group at http://groups.google.com/group/django-users.
To view this discussion on the web visit
https://groups.google.com/d/msgid/django-users/CAHN%3D9D6GktR1aFX-UbbBQhS5WxScQ%2BG8Wpvm9T0Uh%3D1KujkHzw%40mail.gmail.com.
For more options, visit https://groups.google.com/d/optout.
