That isn't what I was looking to do. Here a more detailed explanation.
Datebase setup:
Table 1 - Song:
id | artist | album | title
---+------------+-------------+---------
1 | Artist 1 | Album 1 | Song 1
2 | Artist 1 | Album 2 | Song 1
3 | Artist 1 | Album 3 | Song 1
4 | Artist 2 | Album 4 | Song 2
Table 2 - Played:
played_id | track_id | date_played
--------------+-------------+------------------
1 | 1 | 11:11:11
2 | 2 | 11:11:11
3 | 3 | 11:11:11
4 | 4 | 11:12:12
The reason for this setup is that an artist may have the same song on
several albums (best of, etc.). When a song is played by some DJs, the
album information is not available, so the played information is updated
for all the versions of that artist - title combination. But the
date_played will be the exact same, so that's why I'm trying to key on
getting the info from the database.
The results I'd like are:
id | artist | album | title | date_played | count(date_played)
1 | Artist 1 | Album 1 | Song 1 | 11:11:11 | 3
4 | Artist 2 | Album 4 | Song 2 | 11:12:12 | 1
Where id could be any of the group if there were more than one, album again
any of the group.
I currently have a sql query that I use for this, I was just hoping to get
away from custom queries as I'm moving this project from PHP to Django. The
current PHP code looks to see if the count(date_played) > 1, and instead of
showing the album name, it just shows On (x) albums.
I can't make any changes to the layout of the Song table, but making minor
changes to the Played table is a possibility. (I'd rather not, but if there
is better way to do it, I'm not opposed)
Thanks!
Brian
On Wed, May 15, 2013 at 1:16 AM, Venkatraman S <venka...@gmail.com> wrote:
>
> I assume you want to count the number of records based on the date; or in
> other words, number of times a song is played grouped by date..right?
>
> If yes, does something like this help:
>
> Item.objects.extra({'created':"date(created_date)"}).values('created').annotate(created_count=Count('id')).order_by()
>
> -V
> @venkasub <http://about.me/venkasub>
>
>
> On Wed, May 15, 2013 at 7:09 AM, brian <bmill...@gmail.com> wrote:
>
>> I'm trying to do a query that needs a group_by on a field other than id.
>>
>> I've tried this:
>>
>>
>> s=Song.objects.using('bmillham').annotate(Count('played__date_played')).order_by('-played__date_played')
>>
>> the generated query is:
>>
>> >>> print s.query
>> SELECT `song`.`id`, `song`.`file`, `song`.`catalog`, `song`.`album`,
>> `song`.`album`, `song`.`year`, `song`.`artist`, `song`.`artist`,
>> `song`.`title`, `song`.`bitrate`, `song`.`rate`, `song`.`mode`,
>> `song`.`size`, `song`.`time`, `song`.`track`, `song`.`update_time`,
>> `song`.`addition_time`, COUNT(`played`.`date_played`) AS
>> `played__date_played__count` FROM `song` LEFT OUTER JOIN `played` ON
>> (`song`.`id` = `played`.`track_id`) GROUP BY `song`.`id` ORDER BY
>> `played`.`date_played` DESC
>>
>> I tried adding
>>
>> s.query.group_by = [('played', 'date_played')]
>>
>> but that doesn't change the query.
>>
>> Am I missing something here, or can't this be done?
>>
>> Thanks!
>> Brian
>>
>> --
>> 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?hl=en.
>> For more options, visit https://groups.google.com/groups/opt_out.
>>
>>
>>
> --
> 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?hl=en.
> For more options, visit https://groups.google.com/groups/opt_out.
>
>
>
--
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?hl=en.
For more options, visit https://groups.google.com/groups/opt_out.
