Re: Calculations in Queries

[Melvyn Sopacua]

On 15-8-2012 23:01, Callum Bonnyman wrote:
>
>
> I am creating a popular page for my app and for the most part this is just
> to gain an understanding.
>
> The idea is to divide the number of views by the days old, now i don't know
> if this will be successful or even working but like I said its go get an
> idea of how queries work...
>
> My fields are named: views and created. Below is an idea of what I'm
> thinking of.
>
> (now - created) / views
You probably want views / (now - created) which means the average number
of views over time. Your function above calculates the average time
between views.

> popular_posts = Post.objects.all().order_by('-id')[:50]

The problem isn't the calculation. The problem is that you want the
result of the calculation and the ability to sort with it. If you want
to keep this independent of the database backend I highly suggest you
calculate the value in python and store it in the database.

Otherwise, I don't see a way to avoid using the extra method:
<https://docs.djangoproject.com/en/1.4/ref/models/querysets/#extra>

--
Melvyn Sopacua

[Callum Bonnyman]

I've tried a few things but i cannot get the correct date formats to calculate the difference, so far i have this: 

    popular_posts = Post.objects.extra(select={

    'popularity': '(' + datetime.datetime.now() + ' - created) * views'

    })

It shows an error, i've tried googling it but not had much luck so far

[Kurtis Mullins]

On Wed, Aug 15, 2012 at 8:51 PM, Callum Bonnyman <bonn...@gmail.com> wrote:

I've tried a few things but i cannot get the correct date formats to calculate the difference, so far i have this: 

    popular_posts = Post.objects.extra(select={

    'popularity': '(' + datetime.datetime.now() + ' - created) * views'

    })

It shows an error, i've tried googling it but not had much luck so far 

Where error are you getting?

 

[Sandeep kaur]

On Thu, Aug 16, 2012 at 2:31 AM, Callum Bonnyman <bonn...@gmail.com> wrote:
> I am creating a popular page for my app and for the most part this is just
> to gain an understanding.

<snip>
> Any ideas? I know its something to do with query expressions and looking at
> this page confuses the hell out of me. Any guidelines, or is this even
> achievable?
>
Why don't you use sessions for the same. That is, in a session find
number of clicks on the pages by a user, then save it in database.
Then just get the page that has maximum clicks, that will be the most
popular page.
Just a wild idea.

--
Sandeep Kaur
E-Mail: mkaur...@gmail.com
Blog: sandymadaan.wordpress.com

[akaariai]

On 16 elo, 03:51, Callum Bonnyman <bonnym...@gmail.com> wrote:
> I've tried a few things but i cannot get the correct date formats to
> calculate the difference, so far i have this:
>
>     popular_posts = Post.objects.extra(select={
>     'popularity': '(' + datetime.datetime.now() + ' - created) * views'
>     })
>
> It shows an error, i've tried googling it but not had much luck so far

It seems you have two errors here:
1. you should not use datetime.datetime.now() as part of the
querystring, use select_params (see the docs) to pass the time to the
query.
2. the "timedelta" * views calculation might need some casting. It
might be your DB isn't happy about doing multiplication of an integer
and an interval.

You might want to store the popularity into the model itself. The
problem with your approach is that if you have a lot of posts, you
can't use indexing for the popularity. So, to fetch the top 50 popular
posts, the DB will need to sort all the posts. This will be slow if
you have non-trivial amount of posts.

- Anssi