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.
