Oh sorry, I knew that my sql query was naive, but my question was really
about how to use group by and retrieve data throug my template :/
I'm really sorry for waste your time with stupid sql.
Nevertheless a great thank you for your help.
I'm going to do like you say and in few times, try to change this with a sql
view which is safer (but i heard it's hard to use sql view in the django
ORM)
Thank for your help Bruno :)
On Fri, Jul 15, 2011 at 3:57 PM, bruno desthuilliers <
bruno.desthuilli...@gmail.com> wrote:
> On Jul 15, 2:53 pm, nicolas HERSOG <n.her...@gmail.com> wrote:
> > Of course, and thanks for your help :)
> >
> > This is the rows of my first table Video :
> > id | name | category_id | date
> > 1 | vids1 | 1 | dd-jj-mmmm
> > 2 | vids2 | 1 | dd-jj-mmmm
> > 3 | vids3 | 1 | dd-jj-mmmm
> > 4 | vids4 | 2 | dd-jj-mmmm
> > 5 | vids5 | 2 | dd-jj-mmmm
> > 6 | vids6 | 3 | dd-jj-mmmm
> >
> > and here the rows of the category table :
> > id | name
> > 1 | SF
> > 2 | drama
> > 3 | news
> >
> > I want to retrieve the latest vod by categories, so that s why i thought
> > about the group_by category. So with a group_by i should retrieve :
> > 1 | vids1 | 1
> > 4 | vids4 | 2
> > 6 | vids6 | 3
> > Am i wrong ?
> >
>
> Err... Actually, yes you are.
>
> It might *seems* to work if the "latest" (assuming 'by date') video
> for each category happens to be the one with the lowest id (which FWIW
> is highly unlikely for very obvious reasons), ie:
>
> mysql> select * from video;
> +----+-------+------------+-------------+
> | id | name | date_added | category_id |
> +----+-------+------------+-------------+
> | 1 | vids1 | 2011-07-15 | 1 |
> | 2 | vids2 | 2011-07-14 | 1 |
> | 3 | vids3 | 2011-07-13 | 1 |
> | 4 | vids4 | 2011-07-15 | 2 |
> | 5 | vids5 | 2011-07-14 | 2 |
> | 6 | vids6 | 2011-07-15 | 3 |
> +----+-------+------------+-------------+
>
> mysql> select * from video group by category_id;
> +----+-------+------------+-------------+
> | id | name | date_added | category_id |
> +----+-------+------------+-------------+
> | 1 | vids1 | 2011-07-15 | 1 |
> | 4 | vids4 | 2011-07-15 | 2 |
> | 6 | vids6 | 2011-07-15 | 3 |
> +----+-------+------------+-------------+
>
> But as soon as you add a "new" video it will break:
>
> mysql> insert into video(name, category_id, date_added)
> values("vids7", 1, "2011-07-16");
> Query OK, 1 row affected (0.00 sec)
>
> mysql> select * from video group by category_id;
> +----+-------+------------+-------------+
> | id | name | date_added | category_id |
> +----+-------+------------+-------------+
> | 1 | vids1 | 2011-07-15 | 1 |
> | 4 | vids4 | 2011-07-15 | 2 |
> | 6 | vids6 | 2011-07-15 | 3 |
> +----+-------+------------+-------------+
>
> => expected "vids7" to be the "latest" for category_id 1
>
> What you want (getting the "latest/highest/whatever by some other
> value") in "one" query requires something a bit more involved -
> usually a corelated subquery, ie:
>
> mysql> select v1.* from video v1 where v1.date_added = (select
> max(date_added) from video v2 where v2.category_id=v1.category_id);
> +----+-------+------------+-------------+
> | id | name | date_added | category_id |
> +----+-------+------------+-------------+
> | 4 | vids4 | 2011-07-15 | 2 |
> | 6 | vids6 | 2011-07-15 | 3 |
> | 7 | vids7 | 2011-07-16 | 1 |
> +----+-------+------------+-------------+
> 3 rows in set (0.00 sec)
>
>
> Such a query can be a bit tricky to express using Django's ORM (which
> is meant to help with the most common queries, not to replace SQL),
> and FWIW subqueries can be somewhat inefficient, depending on your SQL
> backend, indexes, dataset and whatnot.
>
> To make a long story short: unless you have a lot of categories (which
> is, once again, very unlikely in this context), you could just start
> with a simpler approach, like
>
> categories = Category.objects.order_by("-video__date_added")
> videos = []
> for category in categories:
> try:
>
>
> videos.append(category.video_set.latest("date_added").select_related('Category))
> except Video.DoesNotExist:
> # no video for this category
> pass
> return dict(videos=videos)
>
> It's clearly suboptimal, but it's simple and should just work, and
> with this code encapsulated in your templatetag (or as a function or
> method in your models, then called from your templatetag), you can
> safely try and optimize this later if and when it becomes a bottleneck
> (or if and when you find time to try and benchmark various
> implementations to find out which is the fastest for your backend and
> dataset).
>
> HTH
>
> --
> You received this message because you are subscribed to the Google Groups
> "Django users" group.
> To post to this group, send email to django-users@googlegroups.com.
> To unsubscribe from this group, send email to
> django-users+unsubscr...@googlegroups.com.
> For more options, visit this group at
> http://groups.google.com/group/django-users?hl=en.
>
>
--
You received this message because you are subscribed to the Google Groups
"Django users" group.
To post to this group, send email to django-users@googlegroups.com.
To unsubscribe from this group, send email to
django-users+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/django-users?hl=en.
