Left outer join with extra conditions in django
.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty,.everyoneloves__bot-mid-leaderboard:empty{ height:90px;width:728px;box-sizing:border-box;
}
I have problems to do this SQL with django queryset+Q objects.
How can I do that without raw query?
SELECT *
FROM Table1 T1
LEFT OUTER JOIN Table2 T2 ON T1.id == T2.fk_id AND (T2.VALUE = 'val1' OR T2.VALUE IS NULL)
django left-join django-orm django-queryset
edited Sep 8 '13 at 9:34
Maxime Lorant
21.6k146578
asked Sep 8 '13 at 9:02
juanpexjuanpex
887
add a comment |
I have problems to do this SQL with django queryset+Q objects.
How can I do that without raw query?
SELECT *
FROM Table1 T1
LEFT OUTER JOIN Table2 T2 ON T1.id == T2.fk_id AND (T2.VALUE = 'val1' OR T2.VALUE IS NULL)
django left-join django-orm django-queryset
edited Sep 8 '13 at 9:34
Maxime Lorant
21.6k146578
asked Sep 8 '13 at 9:02
juanpexjuanpex
887
add a comment |
I have problems to do this SQL with django queryset+Q objects.
How can I do that without raw query?
SELECT *
FROM Table1 T1
LEFT OUTER JOIN Table2 T2 ON T1.id == T2.fk_id AND (T2.VALUE = 'val1' OR T2.VALUE IS NULL)
django left-join django-orm django-queryset
edited Sep 8 '13 at 9:34
Maxime Lorant
21.6k146578
asked Sep 8 '13 at 9:02
juanpexjuanpex
887
I have problems to do this SQL with django queryset+Q objects.
How can I do that without raw query?
SELECT *
FROM Table1 T1
LEFT OUTER JOIN Table2 T2 ON T1.id == T2.fk_id AND (T2.VALUE = 'val1' OR T2.VALUE IS NULL)
django left-join django-orm django-queryset
django left-join django-orm django-queryset
edited Sep 8 '13 at 9:34
Maxime Lorant
21.6k146578
asked Sep 8 '13 at 9:02
juanpexjuanpex
887
edited Sep 8 '13 at 9:34
Maxime Lorant
21.6k146578
asked Sep 8 '13 at 9:02
juanpexjuanpex
887
edited Sep 8 '13 at 9:34
Maxime Lorant
21.6k146578
edited Sep 8 '13 at 9:34
Maxime Lorant
21.6k146578
edited Sep 8 '13 at 9:34
Maxime Lorant
21.6k146578
21.6k146578
asked Sep 8 '13 at 9:02
juanpexjuanpex
887
asked Sep 8 '13 at 9:02
juanpexjuanpex
887
asked Sep 8 '13 at 9:02
juanpexjuanpex
887
887
add a comment |
add a comment |
1 Answer
1
active
oldest
votes
I think you might have been looking for the Prefetch object, but that likely didn't exist 5 years ago. Any way this is one way to implement it now.
You need the filter in both locations to make sure you're getting the appropriate T1 and T2 instances. Then the Prefetch will actually do an additional query to pull all the related T2 instances. The distinct is so that if there are multiple T2 instances, there aren't duplicate T1 instances returned.
from django.db.models import Prefetch
t2_filter = Q(t2_set__value__isnull=True) | Q(t2_set__value='val1')
T1.objects.filter(
t2_filter,
t2_set__isnull=False,
).prefetch_related(
Prefetch(
't2_set',
T2.objects.filter(t2_filter),
to_attr='filtered_t2_set',
)
).distinct()
answered Nov 16 '18 at 19:38
schillingtschillingt
6,08011824
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
});
}
});
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%2f18682065%2fleft-outer-join-with-extra-conditions-in-django%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
I think you might have been looking for the Prefetch object, but that likely didn't exist 5 years ago. Any way this is one way to implement it now.
You need the filter in both locations to make sure you're getting the appropriate T1 and T2 instances. Then the Prefetch will actually do an additional query to pull all the related T2 instances. The distinct is so that if there are multiple T2 instances, there aren't duplicate T1 instances returned.
from django.db.models import Prefetch
t2_filter = Q(t2_set__value__isnull=True) | Q(t2_set__value='val1')
T1.objects.filter(
t2_filter,
t2_set__isnull=False,
).prefetch_related(
Prefetch(
't2_set',
T2.objects.filter(t2_filter),
to_attr='filtered_t2_set',
)
).distinct()
answered Nov 16 '18 at 19:38
schillingtschillingt
6,08011824
add a comment |
I think you might have been looking for the Prefetch object, but that likely didn't exist 5 years ago. Any way this is one way to implement it now.
You need the filter in both locations to make sure you're getting the appropriate T1 and T2 instances. Then the Prefetch will actually do an additional query to pull all the related T2 instances. The distinct is so that if there are multiple T2 instances, there aren't duplicate T1 instances returned.
from django.db.models import Prefetch
t2_filter = Q(t2_set__value__isnull=True) | Q(t2_set__value='val1')
T1.objects.filter(
t2_filter,
t2_set__isnull=False,
).prefetch_related(
Prefetch(
't2_set',
T2.objects.filter(t2_filter),
to_attr='filtered_t2_set',
)
).distinct()
answered Nov 16 '18 at 19:38
schillingtschillingt
6,08011824
add a comment |
I think you might have been looking for the Prefetch object, but that likely didn't exist 5 years ago. Any way this is one way to implement it now.
You need the filter in both locations to make sure you're getting the appropriate T1 and T2 instances. Then the Prefetch will actually do an additional query to pull all the related T2 instances. The distinct is so that if there are multiple T2 instances, there aren't duplicate T1 instances returned.
from django.db.models import Prefetch
t2_filter = Q(t2_set__value__isnull=True) | Q(t2_set__value='val1')
T1.objects.filter(
t2_filter,
t2_set__isnull=False,
).prefetch_related(
Prefetch(
't2_set',
T2.objects.filter(t2_filter),
to_attr='filtered_t2_set',
)
).distinct()
answered Nov 16 '18 at 19:38
schillingtschillingt
6,08011824
I think you might have been looking for the Prefetch object, but that likely didn't exist 5 years ago. Any way this is one way to implement it now.
You need the filter in both locations to make sure you're getting the appropriate T1 and T2 instances. Then the Prefetch will actually do an additional query to pull all the related T2 instances. The distinct is so that if there are multiple T2 instances, there aren't duplicate T1 instances returned.
from django.db.models import Prefetch
t2_filter = Q(t2_set__value__isnull=True) | Q(t2_set__value='val1')
T1.objects.filter(
t2_filter,
t2_set__isnull=False,
).prefetch_related(
Prefetch(
't2_set',
T2.objects.filter(t2_filter),
to_attr='filtered_t2_set',
)
).distinct()
answered Nov 16 '18 at 19:38
schillingtschillingt
6,08011824
answered Nov 16 '18 at 19:38
schillingtschillingt
6,08011824
answered Nov 16 '18 at 19:38
schillingtschillingt
6,08011824
answered Nov 16 '18 at 19:38
schillingtschillingt
6,08011824
6,08011824
add a comment |
add a comment |
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.
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%2f18682065%2fleft-outer-join-with-extra-conditions-in-django%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
