Styjun

How could it be that 80% of townspeople were farmers during the Edo period in Japan?

Why did the UK remove the 'European Union' from its passport?

Do crew rest seats count towards the maximum allowed number of seats per flight attendant?

tikz drawing rectangle discretized with triangle lattices and its centroids

What color to choose as "danger" if the main color of my app is red

Why would company (decision makers) wait for someone to retire, rather than lay them off, when their role is no longer needed?

Polynomial division: Is this trick obvious?

labelled end points on logic diagram

How to continually let my readers know what time it is in my story, in an organic way?

My bread in my bread maker rises and then falls down just after cooking starts

Network latencies between opposite ends of the Earth

Holding rent money for my friend which amounts to over $10k?

Can a tourist shoot a gun in the USA?

UUID type for NEWID()

When did game consoles begin including FPUs?

Were any of the books mentioned in this scene from the movie Hackers real?

Why does SSL Labs now consider CBC suites weak?

Why doesn't Iron Man's action affect this person in Endgame?

Is there an academic word that means "to split hairs over"?

Using chord iii in a chord progression (major key)

Are there microwaves to heat baby food at Brussels airport?

With today's technology, could iron be smelted at La Rinconada?

Does the wearer know what items are in which patch in the Robe of Useful items?

Formal Definition of Dot Product

Filtering on many-to-many relations that fulfill a set of criteria

How to filter objects using multiple-choice in many-to-many relationsHow to check if many-to-many related field contain all of given valuesDjango: How to annotate M2M or OneToMany fields using a SubQuery?Django odd behaviour with Q objects and `and_` (vs `and`)How do I do a not equal in Django queryset filtering?Django filter many-to-many with containsSpecifying Units in DjangoDjango query for many-to-many subset containmentCreate a new model which have all fields of currently existing modelHow to filter a QuerySet with relation fields compared to a (dynamic) listAnnotating Django querysets with a column from the first row of a reverse relationCount rows of a subquery in Django 1.11Django ORM join many to many relation in one queryHow to filter objects using multiple-choice in many-to-many relations

.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty,.everyoneloves__bot-mid-leaderboard:empty height:90px;width:728px;box-sizing:border-box;

1

With the following models:

class OrderOperation(models.Model):
 ordered_articles = models.ManyToManyField(Article,
 through='orders.OrderedArticle')

class OrderedArticle(models.Model):
 order_operation = models.ForeignKey(OrderOperation)
 article = models.ForeignKey(Article)

articles = ... # some queryset containing multiple articles

If I want to find order operations containing at least one article, this works as expected:

OrderOperation.objects.filter(ordered_articles__in=articles)

However, if I want to find order operations with all the articles in the order, what is the correct way to do it?

OrderOperation.objects.filter(ordered_articles=articles) raises a ProgrammingError: more than one row returned by a subquery used as an expression error (I understand why actually).

python django many-to-many django-orm

share|improve this question

edited Apr 9 at 16:32

Endre Both

3,09611322

asked Feb 23 at 11:19

David D.David D.

3,79832771

add a comment | 

1

With the following models:

class OrderOperation(models.Model):
 ordered_articles = models.ManyToManyField(Article,
 through='orders.OrderedArticle')

class OrderedArticle(models.Model):
 order_operation = models.ForeignKey(OrderOperation)
 article = models.ForeignKey(Article)

articles = ... # some queryset containing multiple articles

If I want to find order operations containing at least one article, this works as expected:

OrderOperation.objects.filter(ordered_articles__in=articles)

However, if I want to find order operations with all the articles in the order, what is the correct way to do it?

OrderOperation.objects.filter(ordered_articles=articles) raises a ProgrammingError: more than one row returned by a subquery used as an expression error (I understand why actually).

python django many-to-many django-orm

share|improve this question

edited Apr 9 at 16:32

Endre Both

3,09611322

asked Feb 23 at 11:19

David D.David D.

3,79832771

add a comment | 

With the following models:

class OrderOperation(models.Model):
 ordered_articles = models.ManyToManyField(Article,
 through='orders.OrderedArticle')

class OrderedArticle(models.Model):
 order_operation = models.ForeignKey(OrderOperation)
 article = models.ForeignKey(Article)

articles = ... # some queryset containing multiple articles

If I want to find order operations containing at least one article, this works as expected:

OrderOperation.objects.filter(ordered_articles__in=articles)

However, if I want to find order operations with all the articles in the order, what is the correct way to do it?

OrderOperation.objects.filter(ordered_articles=articles) raises a ProgrammingError: more than one row returned by a subquery used as an expression error (I understand why actually).

python django many-to-many django-orm

share|improve this question

edited Apr 9 at 16:32

Endre Both

3,09611322

asked Feb 23 at 11:19

David D.David D.

3,79832771

class OrderOperation(models.Model):
 ordered_articles = models.ManyToManyField(Article,
 through='orders.OrderedArticle')

class OrderedArticle(models.Model):
 order_operation = models.ForeignKey(OrderOperation)
 article = models.ForeignKey(Article)

OrderOperation.objects.filter(ordered_articles__in=articles)

share|improve this question

edited Apr 9 at 16:32

Endre Both

3,09611322

asked Feb 23 at 11:19

David D.David D.

3,79832771

share|improve this question

edited Apr 9 at 16:32

Endre Both

3,09611322

asked Feb 23 at 11:19

David D.David D.

3,79832771

edited Apr 9 at 16:32

Endre Both

3,09611322

edited Apr 9 at 16:32

Endre Both

3,09611322

asked Feb 23 at 11:19

David D.David D.

3,79832771

asked Feb 23 at 11:19

David D.David D.

3,79832771

2 Answers
2

active

oldest

votes

3

A simple solution:

order_operations = OrderOperation.objects.all()
for article in articles:
 order_operations = order_operations.filter(ordered_articles=article)

It's just one query, but with an inner join per article. For more than a few articles Willem’s more ingenious solution should perform better.

share|improve this answer

answered Feb 23 at 18:22

Endre BothEndre Both

3,09611322

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Thanks! I switch to this as accepted answer as it is simpler to read, ans perform ok when there are a few articles, as you said :)
  
  – David D.
  Feb 26 at 16:34
  

  
  
  
  

add a comment | 

1

We can first construct a set of articles:

articles_set = set(articles)

Next we can count the number of articles related to the OrderOperation that appear in that set, and check if that number is equal to the size of that set, like:

from django.db.models import Count

OrderOperation.objects.filter(
 ordered_articles__in=articles_set
).annotate(
 narticles=Count('ordered_articles')
).filter(
 narticles=len(articles_set)
)

Since in a ManyToManyField, each Article can occur once per OrderOperation, if the number of related Articles that are in the article_set is the same as the number of elements in the article_set, we thus know that the two sets are the same.

This will create a query that looks like:

SELECT orderoperation.*
 COUNT(orderoperation_article.article_id) AS narticle
FROM orderoperation
JOIN orderoperation_article ON orderoperation_id = orderoperation.id
WHERE orderoperation.article_id IN (article_set)
GROUP BY orderoperation.id
HAVING COUNT(orderoperation_article.article_id) = len(article_set)

where the article_set and len(article_set) are of course replaced by the primary keys of the articles in the set, or the number of elements in that set.

share|improve this answer

answered Feb 23 at 11:25

Willem Van OnsemWillem Van Onsem

155k17159245

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Hi :) It seems that filter(ordered_articles__in=articles_set) has the exact same effect than filter(ordered_articles__in=articles). In both cases, it filters order operations that contains at least 1 of the articles.
  
  – David D.
  Feb 23 at 12:01
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  @DavidD.: yes that is correct, we filter on the related set, the point is not the filtering, we then should .annotate(..) the number of articles that satisfy that condition, and then filter on that number, so the .annotate(..).filter(..) part eventually does the trick.
  
  – Willem Van Onsem
  Feb 23 at 12:02
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Thanks a lot, this works well! Unfortunately, I'm unable to use your solution because I oversimplified my question as I'm using subquery. May I ask you to take a look to this question ? Thanks a lot.
  
  – David D.
  Feb 25 at 9:55
  

  
  
  
  

add a comment | 

Your Answer

StackExchange.ifUsing("editor", function ()
StackExchange.using("externalEditor", function ()
StackExchange.using("snippets", function ()
StackExchange.snippets.init();
);
);
, "code-snippets");

StackExchange.ready(function()
var channelOptions =
tags: "".split(" "),
id: "1"
;
initTagRenderer("".split(" "), "".split(" "), channelOptions);

StackExchange.using("externalEditor", function()
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled)
StackExchange.using("snippets", function()
createEditor();
);

else
createEditor();

);

function createEditor()
StackExchange.prepareEditor(
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader:
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
,
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
);



);

draft saved

draft discarded

Sign up or log in

StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);

Sign up using Google

Sign up using Facebook

Sign up using Email and Password

Post as a guest

Name

Email

Required, but never shown

StackExchange.ready(
function ()
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f54841016%2ffiltering-on-many-to-many-relations-that-fulfill-a-set-of-criteria%23new-answer', 'question_page');

);

Post as a guest

Name

Email

Required, but never shown

3

A simple solution:

order_operations = OrderOperation.objects.all()
for article in articles:
 order_operations = order_operations.filter(ordered_articles=article)

It's just one query, but with an inner join per article. For more than a few articles Willem’s more ingenious solution should perform better.

share|improve this answer

answered Feb 23 at 18:22

Endre BothEndre Both

3,09611322

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Thanks! I switch to this as accepted answer as it is simpler to read, ans perform ok when there are a few articles, as you said :)
  
  – David D.
  Feb 26 at 16:34
  

  
  
  
  

add a comment | 

3

A simple solution:

order_operations = OrderOperation.objects.all()
for article in articles:
 order_operations = order_operations.filter(ordered_articles=article)

It's just one query, but with an inner join per article. For more than a few articles Willem’s more ingenious solution should perform better.

share|improve this answer

answered Feb 23 at 18:22

Endre BothEndre Both

3,09611322

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Thanks! I switch to this as accepted answer as it is simpler to read, ans perform ok when there are a few articles, as you said :)
  
  – David D.
  Feb 26 at 16:34
  

  
  
  
  

add a comment | 

A simple solution:

order_operations = OrderOperation.objects.all()
for article in articles:
 order_operations = order_operations.filter(ordered_articles=article)

It's just one query, but with an inner join per article. For more than a few articles Willem’s more ingenious solution should perform better.

share|improve this answer

answered Feb 23 at 18:22

Endre BothEndre Both

3,09611322

order_operations = OrderOperation.objects.all()
for article in articles:
 order_operations = order_operations.filter(ordered_articles=article)

share|improve this answer

answered Feb 23 at 18:22

Endre BothEndre Both

3,09611322

answered Feb 23 at 18:22

Endre BothEndre Both

3,09611322

answered Feb 23 at 18:22

Endre BothEndre Both

3,09611322

1

We can first construct a set of articles:

articles_set = set(articles)

Next we can count the number of articles related to the OrderOperation that appear in that set, and check if that number is equal to the size of that set, like:

from django.db.models import Count

OrderOperation.objects.filter(
 ordered_articles__in=articles_set
).annotate(
 narticles=Count('ordered_articles')
).filter(
 narticles=len(articles_set)
)

Since in a ManyToManyField, each Article can occur once per OrderOperation, if the number of related Articles that are in the article_set is the same as the number of elements in the article_set, we thus know that the two sets are the same.

This will create a query that looks like:

SELECT orderoperation.*
 COUNT(orderoperation_article.article_id) AS narticle
FROM orderoperation
JOIN orderoperation_article ON orderoperation_id = orderoperation.id
WHERE orderoperation.article_id IN (article_set)
GROUP BY orderoperation.id
HAVING COUNT(orderoperation_article.article_id) = len(article_set)

where the article_set and len(article_set) are of course replaced by the primary keys of the articles in the set, or the number of elements in that set.

share|improve this answer

answered Feb 23 at 11:25

Willem Van OnsemWillem Van Onsem

155k17159245

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Hi :) It seems that filter(ordered_articles__in=articles_set) has the exact same effect than filter(ordered_articles__in=articles). In both cases, it filters order operations that contains at least 1 of the articles.
  
  – David D.
  Feb 23 at 12:01
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  @DavidD.: yes that is correct, we filter on the related set, the point is not the filtering, we then should .annotate(..) the number of articles that satisfy that condition, and then filter on that number, so the .annotate(..).filter(..) part eventually does the trick.
  
  – Willem Van Onsem
  Feb 23 at 12:02
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Thanks a lot, this works well! Unfortunately, I'm unable to use your solution because I oversimplified my question as I'm using subquery. May I ask you to take a look to this question ? Thanks a lot.
  
  – David D.
  Feb 25 at 9:55
  

  
  
  
  

add a comment | 

1

We can first construct a set of articles:

articles_set = set(articles)

Next we can count the number of articles related to the OrderOperation that appear in that set, and check if that number is equal to the size of that set, like:

from django.db.models import Count

OrderOperation.objects.filter(
 ordered_articles__in=articles_set
).annotate(
 narticles=Count('ordered_articles')
).filter(
 narticles=len(articles_set)
)

Since in a ManyToManyField, each Article can occur once per OrderOperation, if the number of related Articles that are in the article_set is the same as the number of elements in the article_set, we thus know that the two sets are the same.

This will create a query that looks like:

SELECT orderoperation.*
 COUNT(orderoperation_article.article_id) AS narticle
FROM orderoperation
JOIN orderoperation_article ON orderoperation_id = orderoperation.id
WHERE orderoperation.article_id IN (article_set)
GROUP BY orderoperation.id
HAVING COUNT(orderoperation_article.article_id) = len(article_set)

where the article_set and len(article_set) are of course replaced by the primary keys of the articles in the set, or the number of elements in that set.

share|improve this answer

answered Feb 23 at 11:25

Willem Van OnsemWillem Van Onsem

155k17159245

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Hi :) It seems that filter(ordered_articles__in=articles_set) has the exact same effect than filter(ordered_articles__in=articles). In both cases, it filters order operations that contains at least 1 of the articles.
  
  – David D.
  Feb 23 at 12:01
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  @DavidD.: yes that is correct, we filter on the related set, the point is not the filtering, we then should .annotate(..) the number of articles that satisfy that condition, and then filter on that number, so the .annotate(..).filter(..) part eventually does the trick.
  
  – Willem Van Onsem
  Feb 23 at 12:02
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Thanks a lot, this works well! Unfortunately, I'm unable to use your solution because I oversimplified my question as I'm using subquery. May I ask you to take a look to this question ? Thanks a lot.
  
  – David D.
  Feb 25 at 9:55
  

  
  
  
  

add a comment | 

We can first construct a set of articles:

articles_set = set(articles)

Next we can count the number of articles related to the OrderOperation that appear in that set, and check if that number is equal to the size of that set, like:

from django.db.models import Count

OrderOperation.objects.filter(
 ordered_articles__in=articles_set
).annotate(
 narticles=Count('ordered_articles')
).filter(
 narticles=len(articles_set)
)

Since in a ManyToManyField, each Article can occur once per OrderOperation, if the number of related Articles that are in the article_set is the same as the number of elements in the article_set, we thus know that the two sets are the same.

This will create a query that looks like:

SELECT orderoperation.*
 COUNT(orderoperation_article.article_id) AS narticle
FROM orderoperation
JOIN orderoperation_article ON orderoperation_id = orderoperation.id
WHERE orderoperation.article_id IN (article_set)
GROUP BY orderoperation.id
HAVING COUNT(orderoperation_article.article_id) = len(article_set)

where the article_set and len(article_set) are of course replaced by the primary keys of the articles in the set, or the number of elements in that set.

share|improve this answer

answered Feb 23 at 11:25

Willem Van OnsemWillem Van Onsem

155k17159245

articles_set = set(articles)

from django.db.models import Count

OrderOperation.objects.filter(
 ordered_articles__in=articles_set
).annotate(
 narticles=Count('ordered_articles')
).filter(
 narticles=len(articles_set)
)

SELECT orderoperation.*
 COUNT(orderoperation_article.article_id) AS narticle
FROM orderoperation
JOIN orderoperation_article ON orderoperation_id = orderoperation.id
WHERE orderoperation.article_id IN (article_set)
GROUP BY orderoperation.id
HAVING COUNT(orderoperation_article.article_id) = len(article_set)

share|improve this answer

answered Feb 23 at 11:25

Willem Van OnsemWillem Van Onsem

155k17159245

answered Feb 23 at 11:25

Willem Van OnsemWillem Van Onsem

155k17159245

answered Feb 23 at 11:25

Willem Van OnsemWillem Van Onsem

155k17159245