I should assume then that this is something that the existing Django ORM
cannot model?
-- Ben
On Mon, Sep 8, 2014 at 1:06 PM, Benjamin Scherrey <proteus...@gmail.com>
wrote:
> 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!!
>
--
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%3D9D5mh%2BXyBENe3f5O%2BS66TG4sJU9afM7kZ1xXSi_G8H1Xqw%40mail.gmail.com.
For more options, visit https://groups.google.com/d/optout.
