Login

Fetching top items

Author:
ubernostrum
Posted:
February 25, 2007
Language:
Python
Version:
Pre .96
Tags:
snippets sql managers group-by
Score:
7 (after 7 ratings)

This is a method from the custom manager for the Snippet model used on this site; the basic idea is to be able to ask for the top n "foo", where "foo" is something related to Snippet. For example, you can use top_items('tag') to get the top Tags ordered by how many Snippets are associated with them.

I have a feeling that I could get this down to one query, but haven't yet put in the time for it.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
def top_items(self, item_type, num=5):
    """
    Returns a list of the top ``num`` objects of a particular
    type, based on the number of Snippets associated with them;
    for example, with ``item_type=tag``, returns a list of Tags
    based on how many Snippets they've been used for.
    
    Acceptable values for ``item_type`` are:
    
     * 'author' -- will return the users who have submitted the
       most Snippets.
     * 'language' -- will return the most-used languages.
     * 'tag' -- will return the most-used tags.
    
    """
    # Need all of these up-front so the mapping dictionary can be built
    # correctly.
    from django.contrib.auth.models import User
    from models import Language, Tag, Snippet
    
    # ``_meta`` strikes again.
    object_mapping = {
        'author': {'model': User,
                   'primary_table': User._meta.db_table,
                   'secondary_table': Snippet._meta.db_table },
        'tag': { 'model': Tag,
                 'primary_table': Tag._meta.db_table,
                 'secondary_table': Snippet._meta.get_field('tags').m2m_db_table() },
        'language': {'model': Language,
                     'primary_table': Language._meta.db_table,
                     'secondary_table': Snippet._meta.db_table },
        }
    
    params = object_mapping[item_type]
    query = """SELECT p.id AS object_id, COUNT(*) AS score
    FROM %s p INNER JOIN %s s
    WHERE p.id = s.%s
    GROUP BY object_id
    ORDER BY score DESC""" % (params['primary_table'], params['secondary_table'], item_type + '_id')
    
    from django.db import connection
    cursor = connection.cursor()
    cursor.execute(query, [])
    object_ids = [row[0] for row in cursor.fetchall()[:num]]
    
    # Use ``in_bulk`` here instead of an ``id__in`` lookup, because ``id__in``
    # would clobber the ordering.
    object_dict = params['model']._default_manager.in_bulk(object_ids)
    return [object_dict[object_id] for object_id in object_ids]

More like this

  1. {% renderonce %} template tag by corrr 5 years, 1 month ago
  2. direct to template from a subdir by Scanner 6 years, 9 months ago
  3. SQLLoggerMidleware + infobar by robvdl 7 years, 6 months ago
  4. Making templatetags global to all templates by pryankster 8 years, 3 months ago
  5. Filter on Multiple M2M Objects Simultaneously by axiak 8 years, 4 months ago

Comments

Please login first before commenting.