Re: very interesting query... i need some help
one thing i like about django and python in general is that i can read
to the source code very easy and come up with some sort of a solution.
the reason i want to keep it a queryset is that i want to perform a
filter even further.
i have come up with a rather simple solution but ineficient. the sql
query is this:
class CustomQuery(sql.Query):
"""
Temporary QuerySet used for making a custom sql that can't be done
in the
ordinary way.
"""
def __init__(self, model, connection, first_value, second_value,
detail_id, where=WhereNode):
#sql.Query.__init__(self, model, connection, where=WhereNode )
super(CustomQuery, self).__init__(model, connection,
where=WhereNode)
self.first_value = first_value
self.second_value = second_value
self.detail_id = detail_id
def as_sql(self, with_limits=True, with_col_aliases=False):
return ('select "products_product"."id",
"products_product"."category_id", "products_product"."product_name",
"products_product"."description", "products_product"."code",
"products_product"."price", "products_product"."stock",
"products_product"."warranty", "products_product"."measuring_unit_id",
"products_product"."vat_code_id", "products_product"."deleted" FROM
"products_product" JOIN "products_productdetails" ON
("products_product"."id" = "products_productdetails"."product_id")
WHERE ("products_productdetails"."detail_id" = %s and
"products_productdetails"."detail_value" SIMILAR TO %s AND cast
("products_productdetails"."detail_value" AS Integer) > %s AND cast
("products_productdetails"."detail_value" AS Integer) <%s )',
(self.detail_id,'[1 2 3 4 5 6 7 8 9 0]+', self.first_value,
self.second_value))
i give this custom query to the existing products queryset and all is
well except that i can't to another filter on this... probably because
as_sql is a hard coded function.. :)) at least i have tried hope
you can help
and the question:
"You say you can't change the
field type to something numeric type because have to store "anything"
in
that field, but what do you expect to happen in the filter if
detail_value is "elephant", say? "
it is actually exactly what i want to do. if it's a "elephant" i have
to deal with the situation :)
--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups
"Django users" group.
To post to this group, send email to django-users@googlegroups.com
To unsubscribe from this group, send email to
django-users+unsubscr...@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/django-users?hl=en
-~--~~~~--~~--~--~---
Re: very interesting query... i need some help
On Fri, 2009-08-07 at 06:15 -0700, talpay...@gmail.com wrote: > i have come up with one of the most interesting query i have ever > seen. > > I want to do this : > > products = products.filter(productdetail__detail_value__gte = > first_value) > products = products.filter(productdetail__detail_value__lte = > second_value) > > where first_value and second_value are integers and > "productdetail__detail_value" are stored as a char. > > it is a very simple sql query but how can i do this using django > queryset. the problem is that i need the products to stay as a > queryset... because i need to filter it again if that is the case. So what is the problem you are seeing? At the end of the above two statements, "product" is a queryset, so that part of your problem is solved. > > taking the easy way out and changing the way the table is constructed > is not an option. i have to store any kind of data in "detail_value" > and filter the information. it's a generic filter and this is the only > problem i don't have a solution to. please help. You have a modelling problem, in that you're using "<=" on a character field, which doesn't make sense for generic data. That's not something Django can help you with out of the box. You say you can't change the field type to something numeric type because have to store "anything" in that field, but what do you expect to happen in the filter if detail_value is "elephant", say? It is neither less than, nor greater than, nor equal to a number. It's incomparable. So you're asking Django to read your mind a bit there. Since you're wanting to do something that is highly SQL specific here, you'll want to first work out what the query will look like in raw SQL. I suspect there's going to be either a nested query to only extract numeric values or maybe a coallesce statement or maybe something else. I can't visualise how you might do this, since it's very data dependent. Work out the SQL query directly, perhaps on a simplified model and then let us know what that might be and then we might be able to help convert it into something you can use in the ORM. Or maybe not -- there are (intentionally) limits on what you can do directly in the ORM and the answer for edge-cases is often to write the query directly in SQL. Which means you cannot use it further as a queryset (you could create something that could be used to extra results, easily enough, but further filter() calls, etc, are much harder to inject correctly). Regards, Malcolm --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups "Django users" group. To post to this group, send email to django-users@googlegroups.com To unsubscribe from this group, send email to django-users+unsubscr...@googlegroups.com For more options, visit this group at http://groups.google.com/group/django-users?hl=en -~--~~~~--~~--~--~---
very interesting query... i need some help
i have come up with one of the most interesting query i have ever seen. I want to do this : products = products.filter(productdetail__detail_value__gte = first_value) products = products.filter(productdetail__detail_value__lte = second_value) where first_value and second_value are integers and "productdetail__detail_value" are stored as a char. it is a very simple sql query but how can i do this using django queryset. the problem is that i need the products to stay as a queryset... because i need to filter it again if that is the case. taking the easy way out and changing the way the table is constructed is not an option. i have to store any kind of data in "detail_value" and filter the information. it's a generic filter and this is the only problem i don't have a solution to. please help. --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups "Django users" group. To post to this group, send email to django-users@googlegroups.com To unsubscribe from this group, send email to django-users+unsubscr...@googlegroups.com For more options, visit this group at http://groups.google.com/group/django-users?hl=en -~--~~~~--~~--~--~---
