Django case, when, then on querysets

[Anthony Hawkes]

Hi Guys,

I need to replace results in a queryset and then perform counts and further queries on the set and have been trying to find a way of doing it.

Basically in some but not all cases I get a set of results eg select * from table and then I want to do a: case when column = 'some value' then 'some other value'.

Is there any way using django's queries or at least preserving the ability to continue using the queries using raw sql? (I've had a look at extra() but can't see a way to make it work)

[Tom Evans]

On Tue, May 13, 2014 at 1:00 AM, Anthony Hawkes <lifesi...@gmail.com> wrote:
> Hi Guys,
>
> I need to replace results in a queryset and then perform counts and further
> queries on the set and have been trying to find a way of doing it.

What do you mean by "replace results"?

>
> Basically in some but not all cases I get a set of results eg select * from
> table and then I want to do a: case when column = 'some value' then 'some
> other value'.
>
> Is there any way using django's queries or at least preserving the ability
> to continue using the queries using raw sql? (I've had a look at extra() but
> can't see a way to make it work)
>

I'm not really sure what any of this means..

Let me throw some sample code at you

qs = Model.objects.all()
if foo:
qs = qs.filter(column='some value')
else:
qs = qs.filter(column_b='some other value')
num_results = qs.count()

Is that what you are after?

Cheers

Tom

[Anthony]

Basically when a queryset like instance.annotate(totals=Count('item_description')[:3] like
PC 10
Mac 7
Mouse 5
Keyboard 4

I want to combine PC and Mac as say: Desktop so the results become

Desktop 17
Mac 7
Mouse 5
Keyboard 4
Scanner 3

Ant

--
You received this message because you are subscribed to a topic in the Google Groups "Django users" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/django-users/ZuSxc3YXA3c/unsubscribe.
To unsubscribe from this group and all its topics, send an email to django-users...@googlegroups.com.
To post to this group, send email to django...@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/CAFHbX1J-NT2R5WxHeS6Qaw_ZjZCn6N4g%3DcP6Ta8gVE3MqsBP7Q%40mail.gmail.com.
For more options, visit https://groups.google.com/d/optout.

[Tom Evans]

On Tue, May 13, 2014 at 11:04 PM, Anthony <lifesi...@gmail.com> wrote:
> Basically when a queryset like
> instance.annotate(totals=Count('item_description')[:3] like
> PC 10
> Mac 7
> Mouse 5
> Keyboard 4
>
> I want to combine PC and Mac as say: Desktop so the results become
>
> Desktop 17
> Mac 7
> Mouse 5
> Keyboard 4
> Scanner 3
>
> Ant

Ah right I see, for some reason I didn't get the 'CASE WHEN' reference
in subject, on the same page now.

However, I have to say sorry - this feature isn't in the ORM as far as I know.

The ORM isn't supposed to replace SQL for every query, since
generating a less complex language to replace SQL entirely is an
exercise in futility - once you have all the features, your new
language is no less complex than the original. I guess this is one
such case.

Cheers

Tom

[Anthony]

Thanks for the answer! Do you think it would be practical to let the orm generate the base query and then doing a replace on the column name? I was thinking of this approach as a custom manager method to return a raw result.

This would allow me the generate a case statement for the column and I could dynamically create it.

Ant

--
You received this message because you are subscribed to a topic in the Google Groups "Django users" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/django-users/ZuSxc3YXA3c/unsubscribe.
To unsubscribe from this group and all its topics, send an email to django-users...@googlegroups.com.
To post to this group, send email to django...@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/CAFHbX1%2BFonHK4fuDUSaRbPd6%3DsWDVsisjYs%2BhUMZR7HhA60a2A%40mail.gmail.com.

[Daniel Roseman]

On Wednesday, 14 May 2014 01:20:39 UTC+1, Anthony Hawkes wrote:

Thanks for the answer! Do you think it would be practical to let the orm generate the base query and then doing a replace on the column name? I was thinking of this approach as a custom manager method to return a raw result.

This would allow me the generate a case statement for the column and I could dynamically create it.

Ant

You're thinking of this in the wrong way. Do you really need this data to be generated by the database in the query? Once the original values are out of the db and made into Python objects, you've got a whole powerful object-orientated language available to you to do whatever transformations you need on the data, ie Python. And you needn't do it at a queryset level: each of the elements of the queryset is a model instance, and in the process of accessing any of the values of those instances you're necessarily interacting with the model object, so you can simply add instance methods and call those instead of the original attributes.

--

DR.

[Anthony]

But a query isn't run until I actually do something with the object, by loading it into memory by accessing or iterating over a queryset I'm performing potential time consuming operations. Isn't it better to do whatever work I can on the database? In some cases I could be doing something similar for thousands of records, surely this wouldn't be effective? Or am I simply overestimating the effort required for Python to work with the results?

--

You received this message because you are subscribed to a topic in the Google Groups "Django users" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/django-users/ZuSxc3YXA3c/unsubscribe.
To unsubscribe from this group and all its topics, send an email to django-users...@googlegroups.com.
To post to this group, send email to django...@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/fb2374d5-c928-4322-a0f0-517c24586eb6%40googlegroups.com.

For more options, visit https://groups.google.com/d/optout.

--
Anthony Hawkes
E-Mail: lifesi...@gmail.com
Ph: 0400 372 260