The boring rants of a lazy nerd

Saturday, June 03, 2006

Code/FFDB/Django - Query by Many2Many relations

Neither the docs not the IRC chennel helped much, so I had to hack my way through Django's guts using my non-existent Python skills to create the following generic manager method that finds stuff according to multiple related stuff. The official docs only show how to get e.g. a publication through an article it published, but not how to get a publication that published two articles (or three, or… n). I use it to find a ship involving some characters.

class FooManager(models.Manager):
  def get_by_m2m(self, m2m_field_name, related_obj_ids, specified_only = False):
    """
    Returns QuerySet with objects that are connected to
     all specified "related_obj_ids" through "m2m_field_name".
    Limits result to only those objects that are linked _only_
     to the specified related objects upon request.
    """
    assert len(related_obj_ids) > 0
    meta = self.model._meta
    m2m = [x for x in meta.many_to_many if x.name == m2m_field_name]
    if len(m2m) != 1:
      raise 'ManyToMany Field "%s" not found in model "%s.%s"!' %         (m2m_field_name, meta.app_label, meta.object_name)
    m2m = m2m[0]
    pk_column = meta.pk.column
    m2m_table = m2m.m2m_db_table()
    source_col = m2m.m2m_column_name()
    target_col = m2m.m2m_reverse_name()
    tables = []
    params = []
    where = ['"%s"."%s" = t1."%s"' % (meta.db_table, pk_column, source_col)]
    n = len(related_obj_ids)
    i = 1
    for id in related_obj_ids:
      if type(id) != type(0): id = id.id
      params.append(id)
      tables.append('"%s" t%d' % (m2m_table, i))
      where.append('t%d."%s" = %%d' % (i, target_col))
      if i != n:
        where.append('t%d."%s" = t%d."%s"' % (i, source_col, i + 1, source_col))
      i = i + 1
    if specified_only:
      where.append(
        '%%d = (SELECT COUNT(*) FROM "%s" WHERE "%s" = "%s"."%s")' %           (m2m_table, source_col, meta.db_table, pk_column))
      params.append(n)
    return self.extra(tables=tables, where=where, params=params)

No comments:

About Me

GCS d- s-: a-- C++$ UL++ P+++ L+++ E--- W+++ N o? K? w++$ !O !M !V PS-(+) PE Y+ PGP+(-) t--@ 5++(+++) !X R-- tv-- b+>++ DI+++ D+ G e h! r* y--(-)>+++