Filter on count of ManyToManyField

[daniel...@gmail.com]

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

[Malcolm Tredinnick]

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

[Malcolm Tredinnick]

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

[daniel...@gmail.com]

Hi.

Malcolm Tredinnick wrote:
> 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).

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","models_blog"."region_id","models_blog"."regionname","models_blog"."date_submitted","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

[Malcolm Tredinnick]

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

[daniel...@gmail.com]

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

[Malcolm Tredinnick]

On Sun, 2006-07-16 at 10:09 +0000, daniel...@gmail.com wrote:
> 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".

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.

[...]

> 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).

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

[Carlos Yoder]

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

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