Conditionally change field value for lookup in Django ORM
I'm trying to conditionally change field value during lookup - I have some specific order in mind and I do not want to overwrite field value, just to sort it my way. Let's say, I have classProduct and every class object has product_code field. Now I want to get less than or equal, but it's not trivial - product_code is for most of the time like this A01, B02 and so on and Django lookup lte would work. But now I have fields 0001C01 which I would like to be the biggest value. So during lookup I would like to add 0000 at the begining of every string that does not have this prefix, so it would look like 0001C01, 0000B02, 0000A01.
python django django-orm
asked Nov 19 '18 at 19:16
PotatoBoxPotatoBox
1261221
add a comment |
I'm trying to conditionally change field value during lookup - I have some specific order in mind and I do not want to overwrite field value, just to sort it my way. Let's say, I have classProduct and every class object has product_code field. Now I want to get less than or equal, but it's not trivial - product_code is for most of the time like this A01, B02 and so on and Django lookup lte would work. But now I have fields 0001C01 which I would like to be the biggest value. So during lookup I would like to add 0000 at the begining of every string that does not have this prefix, so it would look like 0001C01, 0000B02, 0000A01.
python django django-orm
asked Nov 19 '18 at 19:16
PotatoBoxPotatoBox
1261221
You can annotate your queryset using database functions like Concat to get a new field to use in your order_by statement
– ivissani
Nov 20 '18 at 0:10
add a comment |
I'm trying to conditionally change field value during lookup - I have some specific order in mind and I do not want to overwrite field value, just to sort it my way. Let's say, I have classProduct and every class object has product_code field. Now I want to get less than or equal, but it's not trivial - product_code is for most of the time like this A01, B02 and so on and Django lookup lte would work. But now I have fields 0001C01 which I would like to be the biggest value. So during lookup I would like to add 0000 at the begining of every string that does not have this prefix, so it would look like 0001C01, 0000B02, 0000A01.
python django django-orm
asked Nov 19 '18 at 19:16
PotatoBoxPotatoBox
1261221
I'm trying to conditionally change field value during lookup - I have some specific order in mind and I do not want to overwrite field value, just to sort it my way. Let's say, I have classProduct and every class object has product_code field. Now I want to get less than or equal, but it's not trivial - product_code is for most of the time like this A01, B02 and so on and Django lookup lte would work. But now I have fields 0001C01 which I would like to be the biggest value. So during lookup I would like to add 0000 at the begining of every string that does not have this prefix, so it would look like 0001C01, 0000B02, 0000A01.
python django django-orm
python django django-orm
asked Nov 19 '18 at 19:16
PotatoBoxPotatoBox
1261221
asked Nov 19 '18 at 19:16
PotatoBoxPotatoBox
1261221
asked Nov 19 '18 at 19:16
PotatoBoxPotatoBox
1261221
asked Nov 19 '18 at 19:16
PotatoBoxPotatoBox
1261221
asked Nov 19 '18 at 19:16
PotatoBoxPotatoBox
1261221
1261221
You can annotate your queryset using database functions like Concat to get a new field to use in your order_by statement
– ivissani
Nov 20 '18 at 0:10
add a comment |
You can annotate your queryset using database functions like Concat to get a new field to use in your order_by statement
– ivissani
Nov 20 '18 at 0:10
You can annotate your queryset using database functions like Concat to get a new field to use in your order_by statement
– ivissani
Nov 20 '18 at 0:10
You can annotate your queryset using database functions like Concat to get a new field to use in your order_by statement
– ivissani
Nov 20 '18 at 0:10
add a comment |
2 Answers
2
active
oldest
votes
You can conditionally annotate your queryset in order to get a new field that has de desired value and then use this field in your filter or order_by clause. For example you could do the following:
from django.db.models import CharField, Value as V, F, Q, Case, When
from django.db.models.functions import Concat
Product.objects.annotate(
new_product_code=Case(
When(product_code__iregex=r'^[A-Z]+.*', # If it starts with letters
then=Concat(V('0000'), 'product_code', output_field=CharField()) # Then prepend four 0's
),
default=F('product_code') # Else, the original value
)
).filter(new_product_code__lte='whatever you like') # Now filter by using your new value
Relevant parts of the documentation are conditional expressions, database functions and QuerySet API reference
answered Nov 20 '18 at 0:34
ivissaniivissani
63657
add a comment |
This sounds fairly straightforward. Fetch the desired Product objects, and for each one, prepend 0000 to product_code if it doesn't start with that string.
products = Product.objects.filter(some_query_expression)
for product in products:
if not product.product_code.startswith('0000'):
product.product_code = '0000' + product.product_code
It's not clear if you want to save this value back to the database, or just use it for temporary comparisons. If you do want to save it, call product.save().
answered Nov 19 '18 at 19:26
John GordonJohn Gordon
9,77451729
I do not want to overwrite the original value nor change it, just to conditionally modify it for the time of lookup.
– PotatoBox
Nov 19 '18 at 19:31
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%2f53381214%2fconditionally-change-field-value-for-lookup-in-django-orm%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
2 Answers
2
active
oldest
votes
2 Answers
2
active
oldest
votes
active
oldest
votes
active
oldest
votes
You can conditionally annotate your queryset in order to get a new field that has de desired value and then use this field in your filter or order_by clause. For example you could do the following:
from django.db.models import CharField, Value as V, F, Q, Case, When
from django.db.models.functions import Concat
Product.objects.annotate(
new_product_code=Case(
When(product_code__iregex=r'^[A-Z]+.*', # If it starts with letters
then=Concat(V('0000'), 'product_code', output_field=CharField()) # Then prepend four 0's
),
default=F('product_code') # Else, the original value
)
).filter(new_product_code__lte='whatever you like') # Now filter by using your new value
Relevant parts of the documentation are conditional expressions, database functions and QuerySet API reference
answered Nov 20 '18 at 0:34
ivissaniivissani
63657
add a comment |
You can conditionally annotate your queryset in order to get a new field that has de desired value and then use this field in your filter or order_by clause. For example you could do the following:
from django.db.models import CharField, Value as V, F, Q, Case, When
from django.db.models.functions import Concat
Product.objects.annotate(
new_product_code=Case(
When(product_code__iregex=r'^[A-Z]+.*', # If it starts with letters
then=Concat(V('0000'), 'product_code', output_field=CharField()) # Then prepend four 0's
),
default=F('product_code') # Else, the original value
)
).filter(new_product_code__lte='whatever you like') # Now filter by using your new value
Relevant parts of the documentation are conditional expressions, database functions and QuerySet API reference
answered Nov 20 '18 at 0:34
ivissaniivissani
63657
add a comment |
You can conditionally annotate your queryset in order to get a new field that has de desired value and then use this field in your filter or order_by clause. For example you could do the following:
from django.db.models import CharField, Value as V, F, Q, Case, When
from django.db.models.functions import Concat
Product.objects.annotate(
new_product_code=Case(
When(product_code__iregex=r'^[A-Z]+.*', # If it starts with letters
then=Concat(V('0000'), 'product_code', output_field=CharField()) # Then prepend four 0's
),
default=F('product_code') # Else, the original value
)
).filter(new_product_code__lte='whatever you like') # Now filter by using your new value
Relevant parts of the documentation are conditional expressions, database functions and QuerySet API reference
answered Nov 20 '18 at 0:34
ivissaniivissani
63657
You can conditionally annotate your queryset in order to get a new field that has de desired value and then use this field in your filter or order_by clause. For example you could do the following:
from django.db.models import CharField, Value as V, F, Q, Case, When
from django.db.models.functions import Concat
Product.objects.annotate(
new_product_code=Case(
When(product_code__iregex=r'^[A-Z]+.*', # If it starts with letters
then=Concat(V('0000'), 'product_code', output_field=CharField()) # Then prepend four 0's
),
default=F('product_code') # Else, the original value
)
).filter(new_product_code__lte='whatever you like') # Now filter by using your new value
Relevant parts of the documentation are conditional expressions, database functions and QuerySet API reference
answered Nov 20 '18 at 0:34
ivissaniivissani
63657
answered Nov 20 '18 at 0:34
ivissaniivissani
63657
answered Nov 20 '18 at 0:34
ivissaniivissani
63657
answered Nov 20 '18 at 0:34
ivissaniivissani
63657
63657
add a comment |
add a comment |
This sounds fairly straightforward. Fetch the desired Product objects, and for each one, prepend 0000 to product_code if it doesn't start with that string.
products = Product.objects.filter(some_query_expression)
for product in products:
if not product.product_code.startswith('0000'):
product.product_code = '0000' + product.product_code
It's not clear if you want to save this value back to the database, or just use it for temporary comparisons. If you do want to save it, call product.save().
answered Nov 19 '18 at 19:26
John GordonJohn Gordon
9,77451729
I do not want to overwrite the original value nor change it, just to conditionally modify it for the time of lookup.
– PotatoBox
Nov 19 '18 at 19:31
add a comment |
This sounds fairly straightforward. Fetch the desired Product objects, and for each one, prepend 0000 to product_code if it doesn't start with that string.
products = Product.objects.filter(some_query_expression)
for product in products:
if not product.product_code.startswith('0000'):
product.product_code = '0000' + product.product_code
It's not clear if you want to save this value back to the database, or just use it for temporary comparisons. If you do want to save it, call product.save().
answered Nov 19 '18 at 19:26
John GordonJohn Gordon
9,77451729
I do not want to overwrite the original value nor change it, just to conditionally modify it for the time of lookup.
– PotatoBox
Nov 19 '18 at 19:31
add a comment |
This sounds fairly straightforward. Fetch the desired Product objects, and for each one, prepend 0000 to product_code if it doesn't start with that string.
products = Product.objects.filter(some_query_expression)
for product in products:
if not product.product_code.startswith('0000'):
product.product_code = '0000' + product.product_code
It's not clear if you want to save this value back to the database, or just use it for temporary comparisons. If you do want to save it, call product.save().
answered Nov 19 '18 at 19:26
John GordonJohn Gordon
9,77451729
This sounds fairly straightforward. Fetch the desired Product objects, and for each one, prepend 0000 to product_code if it doesn't start with that string.
products = Product.objects.filter(some_query_expression)
for product in products:
if not product.product_code.startswith('0000'):
product.product_code = '0000' + product.product_code
It's not clear if you want to save this value back to the database, or just use it for temporary comparisons. If you do want to save it, call product.save().
answered Nov 19 '18 at 19:26
John GordonJohn Gordon
9,77451729
answered Nov 19 '18 at 19:26
John GordonJohn Gordon
9,77451729
answered Nov 19 '18 at 19:26
John GordonJohn Gordon
9,77451729
answered Nov 19 '18 at 19:26
John GordonJohn Gordon
9,77451729
9,77451729
I do not want to overwrite the original value nor change it, just to conditionally modify it for the time of lookup.
– PotatoBox
Nov 19 '18 at 19:31
add a comment |
I do not want to overwrite the original value nor change it, just to conditionally modify it for the time of lookup.
– PotatoBox
Nov 19 '18 at 19:31
I do not want to overwrite the original value nor change it, just to conditionally modify it for the time of lookup.
– PotatoBox
Nov 19 '18 at 19:31
I do not want to overwrite the original value nor change it, just to conditionally modify it for the time of lookup.
– PotatoBox
Nov 19 '18 at 19:31
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%2f53381214%2fconditionally-change-field-value-for-lookup-in-django-orm%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
You can annotate your queryset using database functions like Concat to get a new field to use in your order_by statement
– ivissani
Nov 20 '18 at 0:10