Re: Filter to find closest match
OK, sorted using:
object=pricelist.pricelisttable_set.extra(where=["""(abs(width - %s))
= ( SELECT Min(Abs(pl.width - %s)) FROM table_pricelisttable pl where
name_id=%s)"""], params=[w, w,d.pricelist_id])[0]
The error was missing in subquery where name_id=%s.
Now it works exactly as should.
On 7/31/07, Michael <[EMAIL PROTECTED]> wrote:
> Yes, you've right.
>
> If I put = the query will return None !
> The only query which return what i want is only raw sql:
>
> select min(abs(width-240)) as m,width from table_pricelisttable where
> name_id = 1 group by width order by m limit 1;
> than performing the second query to get object.
>
> like:
> cursor = connection.cursor()
> cursor.execute("select min(abs(width-%s)) as m, width from
> table_pricelisttable where name_id = %s group by width order by m
> limit 1;" , [w,pricelist.id])
> row = cursor.fetchone()
> base=pricelisttable_set.get(width=row[1])
>
>
> On 7/30/07, Tim Chase <[EMAIL PROTECTED]> wrote:
> >
> > > 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]
> >
> > I would be very surprised if it works now as described with ">="
> > rather than "=". It's basically asking for anything where the
> > difference between the target and the width is
> > greater-than-or-equal to the minimum difference. Thus, this
> > looks like it would return your whole dataset. It sounded like
> > you were only interested in those PriceList items where the width
> > was closest to (equal to) the minimum difference.
> >
> > -tim
> >
> >
> >
> >
> >
> >
> > > >
> >
>
>
> --
> --
> Michael
>
--
--
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
-~--~~~~--~~--~--~---
Re: Filter to find closest match
Yes, you've right.
If I put = the query will return None !
The only query which return what i want is only raw sql:
select min(abs(width-240)) as m,width from table_pricelisttable where
name_id = 1 group by width order by m limit 1;
than performing the second query to get object.
like:
cursor = connection.cursor()
cursor.execute("select min(abs(width-%s)) as m, width from
table_pricelisttable where name_id = %s group by width order by m
limit 1;" , [w,pricelist.id])
row = cursor.fetchone()
base=pricelisttable_set.get(width=row[1])
On 7/30/07, Tim Chase <[EMAIL PROTECTED]> wrote:
>
> > 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]
>
> I would be very surprised if it works now as described with ">="
> rather than "=". It's basically asking for anything where the
> difference between the target and the width is
> greater-than-or-equal to the minimum difference. Thus, this
> looks like it would return your whole dataset. It sounded like
> you were only interested in those PriceList items where the width
> was closest to (equal to) the minimum difference.
>
> -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
-~--~~~~--~~--~--~---
Re: Filter to find closest match
> 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] I would be very surprised if it works now as described with ">=" rather than "=". It's basically asking for anything where the difference between the target and the width is greater-than-or-equal to the minimum difference. Thus, this looks like it would return your whole dataset. It sounded like you were only interested in those PriceList items where the width was closest to (equal to) the minimum difference. -tim --~--~-~--~~~---~--~~ 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 -~--~~~~--~~--~--~---
Re: Filter to find closest match
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 -~--~~~~--~~--~--~---
Re: Filter to find closest match
> 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 --~--~-~--~~~---~--~~ 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 -~--~~~~--~~--~--~---
Re: Filter to find closest match
On 7/29/07, Tim Chase <[EMAIL PROTECTED]> wrote: > > > currently I using filter to find closest match: > > step=100 > > w=2000 # get it from forms. > > value=pricelist.pricelisttable_set.filter(width__gte=w-step,width__lte=w+step)[0] > > > > The problem is that "step" is different for different tables so > > sometimes getting more values or none. > > > > What would be the best way to find the closest match? > > I have no idea what the Excel logic (or lack there of) may or may > not have been. Without Excel on my Linux box, it's hard to test, > and even if I did have Excel, there was no data against which to > test and see what the above formula should have done. :) The same formula work on OpenOffice or Gnumeric on my linux box :) ( running Excel on wine ) The whole idea to find the value in DB which is closest to to value which we give at request. let say we have a table: width, heght1, heugth2 100, 210, 310, ... 200, 250, 280, ... 350, 260, 290, ... 520, 420, 460, ... .. Now we'geot some number let say width=240 so the closest match in that case will be 200. 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? -- -- 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 -~--~~~~--~~--~--~---
