Hi.

This is probably trivial, but I've not managed to find the solution.
How do I filter objects based on the count of a ManyToManyField?

My example:

I have two classes -- Blog and Submission:

class Blog( models.Model ):
    entries = models.ManyToManyField( Submission )

class Submission( models.Model ):
  [... whatever ]

I want to fetch a list of all Blog instances which have at least one
Submission , i.e. entries.count() > 0. Changing the model is not an
option.

I've been trying all kinds of permutations of filter, entries, count,
gt, etc, such as:
Blog.objects.filter(entries__count__gt = 0)

No luck so far.

Can somebody please help me?

Thanks,
    Daniel

Hi Daniel,

I'll agree this one isn't too obvious (until you see the answer):

        Blog.objects.filter(entries__isnull = False)

will do what you want (have a look at the underlying SQL query is you
are interested in seeing that it really works as advertised).

Regards,
Malcolm

Note, by the way, that this solution is specific to the case of "entries
exist" (i.e. count > 0). To filter on a different count value would
require a "having" clause (or an equivalent formulation) in the
underlying SQL and Django does no generate those at the moment. So if
you're tempted to try and generalise this, you have to start looking at
custom SQL statements, although they aren't too hard either.

Regards,
Malcolm

Hi.

I'm on the current trunk ( 0.95 (post-magic-removal) )

"entries__isnull=False" gives me:

psycopg.ProgrammingError: FEHLER:  Spalte
m2m_models_blog__entries.entries existiert nicht

SELECT
"models_blog"."id","models_blog"."title","models_blog"."description","model s_blog"."region_id","models_blog"."regionname","models_blog"."date_submitte d","models_blog"."author_id","models_blog"."visible"
FROM "models_blog" LEFT OUTER JOIN "models_blog_entries" AS
"m2m_models_blog__entries" ON "models_blog"."id" =
"m2m_models_blog__entries"."blog_id" WHERE
("m2m_models_blog__entries"."entries" IS NOT NULL)

There's no "m2m...." stuff in the DB that I can find.

  Daniel

Well, first of all m2m_models_blog__entries is an alias (see the "FROM"
clause) for the models_blog_entries table. You should have a table in
your database that looks like models_blog_<something> which contains
three columns: an id, a blog_id and a submission_id (maybe not called
exactly that).

Is it possible that you renamed that attribute after running your
initial syncdb for these models? If not, can you post the names of the
tables for this application, please.

Unfortunately, the example I tested with before posting my reply used a
related model called Tag and an attribute called "tags", so the query
looks pretty believable on my end. However, I'm pretty sure we do name
the join table after the attribute.

Regards,
Malcolm

Hi.

I'm pretty sure I didn't rename anything after creating the DB.

The error message I got was that the column
"m2m_models_blog__entries.entries" doesn't exist. I get the same error
when executing the SQL in psql directly.

I hadn't seen that the m2m stuff was in the "AS" clause. OK, so
m2m_models_blog__entries is an alias for models_blog_entries -- I
*have* this table, but it has no column "entries". THis is what it
looks like:

# \d models_blog_entries

Tabelle »public.models_blog_entries«
    Spalte     |   Typ   |                              Attribute
---------------+---------+------------------------------------------------- --------------------
 id            | integer | not null default
nextval('public.models_blog_entries_id_seq'::text)
 blog_id       | integer | not null
 submission_id | integer | not null
Indexe:
    »models_blog_entries_pkey« PRIMARY KEY, btree (id)
    »models_blog_entries_blog_id_key« UNIQUE, btree (blog_id,
submission_id)
Fremdschlüssel-Constraints:
    »models_blog_entries_blog_id_fkey« FOREIGN KEY (blog_id)
REFERENCES models_blog(id)
    »models_blog_entries_submission_id_fkey« FOREIGN KEY
(submission_id) REFERENCES models_submission(id)

I also have these other blog-related tables in the DB:

 public | models_blog
 public | models_blog_entries
 public | models_blog_entries_id_seq
 public | models_blog_id_seq

If I do this:

Blog.objects.filter(entries__blog__id__isnull = False)

I get a list of Blogs which have entries, however I have duplicates in
the list, one for each Submission which is in a M2M relationship (i.e.
each Blog is shown n times if it has n entries).

Daniel

OK, that's just weird. In my case, the SQL is being correctly converted
to match the "tag_id" column (the equivalent of your "submission_id").
Not sure what's going on there right at the moment.

[...]

That should be fixed by using the distinct() method as well:

        Blog.objects.filter(entries__blog__id__isnull =
        False).distinct()

I hadn't noticed the non-distinct results in my case either, but it
makes sense now that you mention it.

Sorry, I'm not sure why you are seeing the bad SQL being generated; but
at least you have a not-too-painful workaround that gives the same
results. There's a bug in there somewhere. Maybe file a ticket and
somebody will look at it when we have time.

Regards,
Malcolm

I had a similar problem, and just used custom SQL. It's easy:

        from django.db import connection
        cursor = connection.cursor()
        cursor.execute("""
                SELECT cars_model.id, cars_model.opis,
                cars_znamka.id, cars_znamka.opis,
                COUNT(*)
                FROM cars_model, cars_vozilo, cars_znamka
                WHERE cars_model.id=cars_vozilo.model_id
                AND cars_znamka.id = cars_model.znamka_id
                GROUP BY cars_model.id, cars_model.opis, cars_znamka.id, cars_znamka.opis
                HAVING COUNT(*)>0
        """)
        result_list = []
        for row in cursor.fetchall():
                # create the dictionary object, that you'll pass to your template.
                 dict_obj = {
                        "id" : row[0],
                        "opis" : "%s %s" % (row[3], row[1]),
                        "cars_count":row[4]
                 }
                 result_list.append(dict_obj)

        # template expects "model_list"
        model_list = result_list

Hope this helps,

Carlos

On 7/16/06, daniel.tie...@gmail.com <daniel.tie...@gmail.com> wrote:

--
Carlos Yoder
http://carlitosyoder.blogspot.com