How to create another column with filtered value in queryset
I have a query that retrieves 20 questions. The query then should add another column or data to the queryset that indicates which choice the user has answered. So in total there are 4 models, including a User model.
class Question(models.Model):
user = models.ForeignKey(User, on_delete=models.CASCADE)
status = models.CharField(max_length=200)
total_votes = models.IntegerField(default=0)
class Choice(models.Model):
question = models.ForeignKey(Question, on_delete=models.CASCADE)
choice = models.CharField(max_length=120)
vote_count = models.IntegerField(default=0)
class Voting(models.Model):
user = models.ForeignKey(User, on_delete=models.CASCADE)
choice = models.ForeignKey(Choice, on_delete=models.CASCADE)
Query
Question.objects.filter(
Q(user=3))[:20]
When I do this, it doesn't work:
Question.objects.filter(Q(user=1)).annotate(which_answer= filter(choice__question=3, user=1))[:20]
I get TypeError: filter() does not take keyword arguments
How do I add another column in this query to show which choice the user picked?
python django django-queryset
edited Nov 20 at 21:04
D Manokhin
588219
asked Nov 20 at 20:47
user2896120
9301021
add a comment |
I have a query that retrieves 20 questions. The query then should add another column or data to the queryset that indicates which choice the user has answered. So in total there are 4 models, including a User model.
class Question(models.Model):
user = models.ForeignKey(User, on_delete=models.CASCADE)
status = models.CharField(max_length=200)
total_votes = models.IntegerField(default=0)
class Choice(models.Model):
question = models.ForeignKey(Question, on_delete=models.CASCADE)
choice = models.CharField(max_length=120)
vote_count = models.IntegerField(default=0)
class Voting(models.Model):
user = models.ForeignKey(User, on_delete=models.CASCADE)
choice = models.ForeignKey(Choice, on_delete=models.CASCADE)
Query
Question.objects.filter(
Q(user=3))[:20]
When I do this, it doesn't work:
Question.objects.filter(Q(user=1)).annotate(which_answer= filter(choice__question=3, user=1))[:20]
I get TypeError: filter() does not take keyword arguments
How do I add another column in this query to show which choice the user picked?
python django django-queryset
edited Nov 20 at 21:04
D Manokhin
588219
asked Nov 20 at 20:47
user2896120
9301021
Check out aggregates and annotations docs.djangoproject.com/en/2.1/topics/db/aggregation
– Victor 'Chris' Cabral
Nov 20 at 20:57
@Victor'Chris'Cabral Hmm this is aggregating though. I don't want to aggregate. I want to actually retrieve which choice id the user answered. I'm trying this query:Question.objects.filter(Q(user=1)).annotate(which_answer= filter(choice__question=3, user=1))but it is not working
– user2896120
Nov 20 at 20:59
@user2896120 what is the SQL query you want? Let's start there.
– Victor 'Chris' Cabral
Nov 20 at 21:09
add a comment |
I have a query that retrieves 20 questions. The query then should add another column or data to the queryset that indicates which choice the user has answered. So in total there are 4 models, including a User model.
class Question(models.Model):
user = models.ForeignKey(User, on_delete=models.CASCADE)
status = models.CharField(max_length=200)
total_votes = models.IntegerField(default=0)
class Choice(models.Model):
question = models.ForeignKey(Question, on_delete=models.CASCADE)
choice = models.CharField(max_length=120)
vote_count = models.IntegerField(default=0)
class Voting(models.Model):
user = models.ForeignKey(User, on_delete=models.CASCADE)
choice = models.ForeignKey(Choice, on_delete=models.CASCADE)
Query
Question.objects.filter(
Q(user=3))[:20]
When I do this, it doesn't work:
Question.objects.filter(Q(user=1)).annotate(which_answer= filter(choice__question=3, user=1))[:20]
I get TypeError: filter() does not take keyword arguments
How do I add another column in this query to show which choice the user picked?
python django django-queryset
edited Nov 20 at 21:04
D Manokhin
588219
asked Nov 20 at 20:47
user2896120
9301021
I have a query that retrieves 20 questions. The query then should add another column or data to the queryset that indicates which choice the user has answered. So in total there are 4 models, including a User model.
class Question(models.Model):
user = models.ForeignKey(User, on_delete=models.CASCADE)
status = models.CharField(max_length=200)
total_votes = models.IntegerField(default=0)
class Choice(models.Model):
question = models.ForeignKey(Question, on_delete=models.CASCADE)
choice = models.CharField(max_length=120)
vote_count = models.IntegerField(default=0)
class Voting(models.Model):
user = models.ForeignKey(User, on_delete=models.CASCADE)
choice = models.ForeignKey(Choice, on_delete=models.CASCADE)
Query
Question.objects.filter(
Q(user=3))[:20]
When I do this, it doesn't work:
Question.objects.filter(Q(user=1)).annotate(which_answer= filter(choice__question=3, user=1))[:20]
I get TypeError: filter() does not take keyword arguments
How do I add another column in this query to show which choice the user picked?
python django django-queryset
python django django-queryset
edited Nov 20 at 21:04
D Manokhin
588219
asked Nov 20 at 20:47
user2896120
9301021
edited Nov 20 at 21:04
D Manokhin
588219
asked Nov 20 at 20:47
user2896120
9301021
edited Nov 20 at 21:04
D Manokhin
588219
edited Nov 20 at 21:04
D Manokhin
588219
edited Nov 20 at 21:04
D Manokhin
588219
588219
asked Nov 20 at 20:47
user2896120
9301021
asked Nov 20 at 20:47
user2896120
9301021
asked Nov 20 at 20:47
user2896120
9301021
9301021
Check out aggregates and annotations docs.djangoproject.com/en/2.1/topics/db/aggregation
– Victor 'Chris' Cabral
Nov 20 at 20:57
@Victor'Chris'Cabral Hmm this is aggregating though. I don't want to aggregate. I want to actually retrieve which choice id the user answered. I'm trying this query:Question.objects.filter(Q(user=1)).annotate(which_answer= filter(choice__question=3, user=1))but it is not working
– user2896120
Nov 20 at 20:59
@user2896120 what is the SQL query you want? Let's start there.
– Victor 'Chris' Cabral
Nov 20 at 21:09
add a comment |
Check out aggregates and annotations docs.djangoproject.com/en/2.1/topics/db/aggregation
– Victor 'Chris' Cabral
Nov 20 at 20:57
@Victor'Chris'Cabral Hmm this is aggregating though. I don't want to aggregate. I want to actually retrieve which choice id the user answered. I'm trying this query:Question.objects.filter(Q(user=1)).annotate(which_answer= filter(choice__question=3, user=1))but it is not working
– user2896120
Nov 20 at 20:59
@user2896120 what is the SQL query you want? Let's start there.
– Victor 'Chris' Cabral
Nov 20 at 21:09
Check out aggregates and annotations docs.djangoproject.com/en/2.1/topics/db/aggregation
– Victor 'Chris' Cabral
Nov 20 at 20:57
Check out aggregates and annotations docs.djangoproject.com/en/2.1/topics/db/aggregation
– Victor 'Chris' Cabral
Nov 20 at 20:57
@Victor'Chris'Cabral Hmm this is aggregating though. I don't want to aggregate. I want to actually retrieve which choice id the user answered. I'm trying this query:
Question.objects.filter(Q(user=1)).annotate(which_answer= filter(choice__question=3, user=1)) but it is not working– user2896120
Nov 20 at 20:59
@Victor'Chris'Cabral Hmm this is aggregating though. I don't want to aggregate. I want to actually retrieve which choice id the user answered. I'm trying this query:
Question.objects.filter(Q(user=1)).annotate(which_answer= filter(choice__question=3, user=1)) but it is not working– user2896120
Nov 20 at 20:59
@user2896120 what is the SQL query you want? Let's start there.
– Victor 'Chris' Cabral
Nov 20 at 21:09
@user2896120 what is the SQL query you want? Let's start there.
– Victor 'Chris' Cabral
Nov 20 at 21:09
add a comment |
1 Answer
1
active
oldest
votes
The annotation would need to look something like this (if user id = 1):
choice_query = Subquery(Choice.objects.filter(question=OuterRef('pk')).values('choice')[:1], output_field=models.CharField())
Question.objects.filter(user=1).annotate(which_answer=choice_query)[:20]
You write a subquery that filters the choice objects based on which question is being annotated and the user, selects just 1 (assuming theres only supposed to be 1) and outputs that choice value to a char field, then you annotate with that subquery.
just a sidenote: that Q expression seems unneeded here.
Not sure what you're use case here is exactly, but you also might see more luck or better performance if you're using serializers and just add a select related clause to your main query and don't bother with the annotation.
Edit: after discussion, I believe what you actually want is the user's VOTE, which points at their choice. It would look like this (again user id = 1):
vote_query = Subquery(Vote.objects.filter(question=OuterRef('pk'), user=1).values('choice')[:1], output_field=models.IntegerField())
Question.objects.filter(user=1).annotate(which_answer=vote_query)[:20]
This query will output the id of the users selected choice to the which_answer annotation.
answered Nov 20 at 21:14
bryan60
5,1391817
Hmm, how would I do it with a serializer? I thought first I had to filter the query for the serializer to use?
– user2896120
Nov 20 at 21:30
I'm really only familiar with the serializers in django rest framework, but they automatically fetch related infomration so long as you declare the relationship in your model serializer. Just make sure to prefetch / select related otherwise it's a preformance killer. If you try it out with serializers but can't get it, just post another question and link me to it.
– bryan60
Nov 20 at 21:31
Yes I am using the Django REST framework. However, how would I show which choice the logged in user selected?
– user2896120
Nov 20 at 21:33
this answer should help: stackoverflow.com/questions/28309507/…
– bryan60
Nov 20 at 21:44
also did my answer work for your original question re annotating? I didn't test it but i've written very similar queries that work fine.
– bryan60
Nov 20 at 22:04
|
show 13 more comments
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
});
}
});
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
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53401253%2fhow-to-create-another-column-with-filtered-value-in-queryset%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
The annotation would need to look something like this (if user id = 1):
choice_query = Subquery(Choice.objects.filter(question=OuterRef('pk')).values('choice')[:1], output_field=models.CharField())
Question.objects.filter(user=1).annotate(which_answer=choice_query)[:20]
You write a subquery that filters the choice objects based on which question is being annotated and the user, selects just 1 (assuming theres only supposed to be 1) and outputs that choice value to a char field, then you annotate with that subquery.
just a sidenote: that Q expression seems unneeded here.
Not sure what you're use case here is exactly, but you also might see more luck or better performance if you're using serializers and just add a select related clause to your main query and don't bother with the annotation.
Edit: after discussion, I believe what you actually want is the user's VOTE, which points at their choice. It would look like this (again user id = 1):
vote_query = Subquery(Vote.objects.filter(question=OuterRef('pk'), user=1).values('choice')[:1], output_field=models.IntegerField())
Question.objects.filter(user=1).annotate(which_answer=vote_query)[:20]
This query will output the id of the users selected choice to the which_answer annotation.
answered Nov 20 at 21:14
bryan60
5,1391817
Hmm, how would I do it with a serializer? I thought first I had to filter the query for the serializer to use?
– user2896120
Nov 20 at 21:30
I'm really only familiar with the serializers in django rest framework, but they automatically fetch related infomration so long as you declare the relationship in your model serializer. Just make sure to prefetch / select related otherwise it's a preformance killer. If you try it out with serializers but can't get it, just post another question and link me to it.
– bryan60
Nov 20 at 21:31
Yes I am using the Django REST framework. However, how would I show which choice the logged in user selected?
– user2896120
Nov 20 at 21:33
this answer should help: stackoverflow.com/questions/28309507/…
– bryan60
Nov 20 at 21:44
also did my answer work for your original question re annotating? I didn't test it but i've written very similar queries that work fine.
– bryan60
Nov 20 at 22:04
|
show 13 more comments
The annotation would need to look something like this (if user id = 1):
choice_query = Subquery(Choice.objects.filter(question=OuterRef('pk')).values('choice')[:1], output_field=models.CharField())
Question.objects.filter(user=1).annotate(which_answer=choice_query)[:20]
You write a subquery that filters the choice objects based on which question is being annotated and the user, selects just 1 (assuming theres only supposed to be 1) and outputs that choice value to a char field, then you annotate with that subquery.
just a sidenote: that Q expression seems unneeded here.
Not sure what you're use case here is exactly, but you also might see more luck or better performance if you're using serializers and just add a select related clause to your main query and don't bother with the annotation.
Edit: after discussion, I believe what you actually want is the user's VOTE, which points at their choice. It would look like this (again user id = 1):
vote_query = Subquery(Vote.objects.filter(question=OuterRef('pk'), user=1).values('choice')[:1], output_field=models.IntegerField())
Question.objects.filter(user=1).annotate(which_answer=vote_query)[:20]
This query will output the id of the users selected choice to the which_answer annotation.
answered Nov 20 at 21:14
bryan60
5,1391817
Hmm, how would I do it with a serializer? I thought first I had to filter the query for the serializer to use?
– user2896120
Nov 20 at 21:30
I'm really only familiar with the serializers in django rest framework, but they automatically fetch related infomration so long as you declare the relationship in your model serializer. Just make sure to prefetch / select related otherwise it's a preformance killer. If you try it out with serializers but can't get it, just post another question and link me to it.
– bryan60
Nov 20 at 21:31
Yes I am using the Django REST framework. However, how would I show which choice the logged in user selected?
– user2896120
Nov 20 at 21:33
this answer should help: stackoverflow.com/questions/28309507/…
– bryan60
Nov 20 at 21:44
also did my answer work for your original question re annotating? I didn't test it but i've written very similar queries that work fine.
– bryan60
Nov 20 at 22:04
|
show 13 more comments
The annotation would need to look something like this (if user id = 1):
choice_query = Subquery(Choice.objects.filter(question=OuterRef('pk')).values('choice')[:1], output_field=models.CharField())
Question.objects.filter(user=1).annotate(which_answer=choice_query)[:20]
You write a subquery that filters the choice objects based on which question is being annotated and the user, selects just 1 (assuming theres only supposed to be 1) and outputs that choice value to a char field, then you annotate with that subquery.
just a sidenote: that Q expression seems unneeded here.
Not sure what you're use case here is exactly, but you also might see more luck or better performance if you're using serializers and just add a select related clause to your main query and don't bother with the annotation.
Edit: after discussion, I believe what you actually want is the user's VOTE, which points at their choice. It would look like this (again user id = 1):
vote_query = Subquery(Vote.objects.filter(question=OuterRef('pk'), user=1).values('choice')[:1], output_field=models.IntegerField())
Question.objects.filter(user=1).annotate(which_answer=vote_query)[:20]
This query will output the id of the users selected choice to the which_answer annotation.
answered Nov 20 at 21:14
bryan60
5,1391817
The annotation would need to look something like this (if user id = 1):
choice_query = Subquery(Choice.objects.filter(question=OuterRef('pk')).values('choice')[:1], output_field=models.CharField())
Question.objects.filter(user=1).annotate(which_answer=choice_query)[:20]
You write a subquery that filters the choice objects based on which question is being annotated and the user, selects just 1 (assuming theres only supposed to be 1) and outputs that choice value to a char field, then you annotate with that subquery.
just a sidenote: that Q expression seems unneeded here.
Not sure what you're use case here is exactly, but you also might see more luck or better performance if you're using serializers and just add a select related clause to your main query and don't bother with the annotation.
Edit: after discussion, I believe what you actually want is the user's VOTE, which points at their choice. It would look like this (again user id = 1):
vote_query = Subquery(Vote.objects.filter(question=OuterRef('pk'), user=1).values('choice')[:1], output_field=models.IntegerField())
Question.objects.filter(user=1).annotate(which_answer=vote_query)[:20]
This query will output the id of the users selected choice to the which_answer annotation.
answered Nov 20 at 21:14
bryan60
5,1391817
edited Nov 21 at 21:29
answered Nov 20 at 21:14
bryan60
5,1391817
answered Nov 20 at 21:14
bryan60
5,1391817
answered Nov 20 at 21:14
bryan60
5,1391817
5,1391817
Hmm, how would I do it with a serializer? I thought first I had to filter the query for the serializer to use?
– user2896120
Nov 20 at 21:30
I'm really only familiar with the serializers in django rest framework, but they automatically fetch related infomration so long as you declare the relationship in your model serializer. Just make sure to prefetch / select related otherwise it's a preformance killer. If you try it out with serializers but can't get it, just post another question and link me to it.
– bryan60
Nov 20 at 21:31
Yes I am using the Django REST framework. However, how would I show which choice the logged in user selected?
– user2896120
Nov 20 at 21:33
this answer should help: stackoverflow.com/questions/28309507/…
– bryan60
Nov 20 at 21:44
also did my answer work for your original question re annotating? I didn't test it but i've written very similar queries that work fine.
– bryan60
Nov 20 at 22:04
|
show 13 more comments
Hmm, how would I do it with a serializer? I thought first I had to filter the query for the serializer to use?
– user2896120
Nov 20 at 21:30
I'm really only familiar with the serializers in django rest framework, but they automatically fetch related infomration so long as you declare the relationship in your model serializer. Just make sure to prefetch / select related otherwise it's a preformance killer. If you try it out with serializers but can't get it, just post another question and link me to it.
– bryan60
Nov 20 at 21:31
Yes I am using the Django REST framework. However, how would I show which choice the logged in user selected?
– user2896120
Nov 20 at 21:33
this answer should help: stackoverflow.com/questions/28309507/…
– bryan60
Nov 20 at 21:44
also did my answer work for your original question re annotating? I didn't test it but i've written very similar queries that work fine.
– bryan60
Nov 20 at 22:04
Hmm, how would I do it with a serializer? I thought first I had to filter the query for the serializer to use?
– user2896120
Nov 20 at 21:30
Hmm, how would I do it with a serializer? I thought first I had to filter the query for the serializer to use?
– user2896120
Nov 20 at 21:30
I'm really only familiar with the serializers in django rest framework, but they automatically fetch related infomration so long as you declare the relationship in your model serializer. Just make sure to prefetch / select related otherwise it's a preformance killer. If you try it out with serializers but can't get it, just post another question and link me to it.
– bryan60
Nov 20 at 21:31
I'm really only familiar with the serializers in django rest framework, but they automatically fetch related infomration so long as you declare the relationship in your model serializer. Just make sure to prefetch / select related otherwise it's a preformance killer. If you try it out with serializers but can't get it, just post another question and link me to it.
– bryan60
Nov 20 at 21:31
Yes I am using the Django REST framework. However, how would I show which choice the logged in user selected?
– user2896120
Nov 20 at 21:33
Yes I am using the Django REST framework. However, how would I show which choice the logged in user selected?
– user2896120
Nov 20 at 21:33
this answer should help: stackoverflow.com/questions/28309507/…
– bryan60
Nov 20 at 21:44
this answer should help: stackoverflow.com/questions/28309507/…
– bryan60
Nov 20 at 21:44
also did my answer work for your original question re annotating? I didn't test it but i've written very similar queries that work fine.
– bryan60
Nov 20 at 22:04
also did my answer work for your original question re annotating? I didn't test it but i've written very similar queries that work fine.
– bryan60
Nov 20 at 22:04
|
show 13 more comments
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Some of your past answers have not been well-received, and you're in danger of being blocked from answering.
Please pay close attention to the following guidance:
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
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
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53401253%2fhow-to-create-another-column-with-filtered-value-in-queryset%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
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
Required, but never shown
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
Required, but never shown
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
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Check out aggregates and annotations docs.djangoproject.com/en/2.1/topics/db/aggregation
– Victor 'Chris' Cabral
Nov 20 at 20:57
@Victor'Chris'Cabral Hmm this is aggregating though. I don't want to aggregate. I want to actually retrieve which choice id the user answered. I'm trying this query:
Question.objects.filter(Q(user=1)).annotate(which_answer= filter(choice__question=3, user=1))but it is not working– user2896120
Nov 20 at 20:59
@user2896120 what is the SQL query you want? Let's start there.
– Victor 'Chris' Cabral
Nov 20 at 21:09