Query returning all items with no M2M 'to_*_id' relationships

[naitsirhc]

Hello,

I have a model that has the following M2M field:

....
myitems = models.ManyToManyField("self", symmetrical=False)
....

Note that this is a non-symmetrical M2M field, so the connections are
directed and thus have a 'from_*_id' and a 'to_*_id'. I would like to
be able to perform a query that returns all instances of the model
which belong to no relationship as a 'to_*_id'. I am using this field
to create a directed tree in my app, connecting nodes. This query
would effectively return any orphan node, or any node that doesn't have
a connection from another node to it.

I know I could do this with custom SQL, but I was wondering if there is
any way to do this using the Django db API. I could also iterate
through the entire object list and check the 'count' of each
'myitems_set', but that would be VERY inefficient.

Any help would be appreciated!

Thanks,
Christian

[naitsirhc]

Hey everyone,

I did a little more digging and came up with a solution by extending
the default manager to add a new function 'get_orphans'. When passed a
M2M non-symmetrical field, it will do a query and return a query set
containing all items which have no parent - ie. any item that does not
appear as an id in the 'to_*_id' field in the M2M relation db. Below
is the manager code:

from django.db import connection, backend, models
from django.core.exceptions import ObjectDoesNotExist
import sys

class HierarchyManager(models.Manager):
def get_orphans(self, field_name, exclude_list=None):

reln = self.model._meta.get_field(field_name)
to_model_type = reln.rel.to
id_field = backend.quote_name('id')
model_table = backend.quote_name(to_model_type._meta.db_table)
join_table = backend.quote_name(reln.m2m_db_table())
#from_field = backend.quote_name(reln.m2m_column_name())
to_field = backend.quote_name(reln.m2m_reverse_name())

query = """
SELECT %s.%s FROM %s
LEFT JOIN %s ON
%s.%s = %s.%s
WHERE
%s.%s IS NULL""" % (
model_table, id_field, model_table,
join_table,
model_table, id_field, join_table, to_field,
join_table, to_field, )
if exclude_list:
query += """
AND NOT %s.%s IN (%s)
""" % ( model_table, id_field, ",".join([str(item) for item in
exclude_list]) )

cursor = connection.cursor()
cursor.execute(query)

orphan_ids = [item[0] for item in cursor.fetchall()]
return self.filter(id__in = orphan_ids)

This seems to be working quite well, and stays generic enough to use
for any M2M non-symmetrical field for a given model. Any thoughts,
comments, criticisms?

Thanks,
Christian

[Russell Keith-Magee]

On 12/12/06, naitsirhc <cfo...@gmail.com> wrote:

> This seems to be working quite well, and stays generic enough to use
> for any M2M non-symmetrical field for a given model. Any thoughts,
> comments, criticisms?

Obviously, it would be nice to be able to support this sort of thing
without having to resort to custom SQL and custom managers, but with
Django as-is, this looks like a reasonable enough solution to the
problem.

There was some recent talk about adding aggregate clauses to the
Django query language. With richer aggregates, 'orphan finding'
(amongst many other things) would be much easier; however, discussion
didn't get much further than the proposal phase.

Yours,
Russ Magee %-)