Login

Fetching top items

Author:
ubernostrum
Posted:
February 25, 2007
Language:
Python
Version:
Pre .96
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. Template tag - list punctuation for a list of items by shapiromatron 11 months, 1 week ago
  2. JSONRequestMiddleware adds a .json() method to your HttpRequests by cdcarter 11 months, 2 weeks ago
  3. Serializer factory with Django Rest Framework by julio 1 year, 6 months ago
  4. Image compression before saving the new model / work with JPG, PNG by Schleidens 1 year, 6 months ago
  5. Help text hyperlinks by sa2812 1 year, 7 months ago

Comments

Please login first before commenting.