Thank you Tim, Seems to work now with: w=240 pricelisttable_set.extra(where=["""(table_pricelisttable.width - %s) >= ( SELECT Min(Abs(pl.width - %s)) FROM table_pricelisttable pl) """], params=[w, w])[0]
On 7/29/07, Tim Chase <[EMAIL PROTECTED]> wrote: > > > In pure sql we can do : > > "select min(abs(width-150)) from quote_pricelisttable where name_id = > > 1 and width >= 240-150 and width <=240+150;" > > > > How to make it work properly from django ORM? > > Did you try playing with the extra() call I included in my > previous email? It should do what you describe, and doesn't > require a fixed window such as you describe not wanting. > > While it does rely on using some SQL, it does so through the > Django ORM and it should be fairly portable SQL (the "Abs()" > function is usually the "Abs()" function on most SQL engines). > The code I included (minus any syntax errors) should have found > the items from the given table where they were the minimum > distance from the target value. > > w = 240 # target width to find the closest...comes from user > results = PriceList.objects.extra(where=[""" > Abs(app_pricelist.width - %s) = ( > SELECT Min(Abs(pl.width - %s)) > FROM app_pricelist pl > ) > """], params=[w, w]) > > The results are a Django ORM query object that can be further > filtered, sliced, sorted, and iterated over with no problems. > While it can be done in pure python/django rather than SQL, it > would require dragging the whole PriceList across the wire and > filtering locally--a much slower proposition than simply letting > the DB do the work for you and shipping you just the exact > results you want. > > -tim > > > > > > > -- -- Michael --~--~---------~--~----~------------~-------~--~----~ 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 [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/django-users?hl=en -~----------~----~----~----~------~----~------~--~---
