Filter to find closest match

Hi group,

I am converting one old application from excel to django.
Almost all sorted but can't figure out how to find closest
math in
query like in excel:
"=INDEX(TABLE5,MATCH(C203,CMW,1),MATCH(Sheet1!J26,CMH,1
))"

All tables in models relate to pricelisttable

class PriceListTable(models.Model):
    name=models.ForeignKey(PriceList,edit_inline=True)
    width = models.IntegerField(core=True)
    height1 = models.IntegerField(null=True, blank=True)
    height2 = models.IntegerField(null=True, blank=True)
    height3 = models.IntegerField(null=True, blank=True)
    height4 = models.IntegerField(null=True, blank=True)
    height5 = models.IntegerField(null=True, blank=True)
    height6 = models.IntegerField(null=True, blank=True)

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?


-- 
--
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-usersgooglegroups.com
To unsubscribe from this group, send email to
django-users-unsubscribegooglegroups.com
For more options, visit this group at htt
p://groups.google.com/group/django-users?hl=en
-~----------~----~----~----~------~----~------~--~---

> 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.  

So, that said, I'm gonna try and pull a guess out of
my...uh...back pocket 

"Closest" often has a mathematical definition akin
to
"minimization of the squares of the differences". 
Thus, you want
to find the minimum of "(w-width)*(w-width)" (this
works nicely
in both SQL and in Django...if done in pure Django, you can
use
"(w-width)**2"). For the logic at hand, it's also
possible to use
an absolute-value function (the abs() function doesn't play
nicely in math generally because of the abrupt change at 0,
while
the squares method makes for a smooth graph...but for
purposes
here, abs() should work just fine).

For a Django ORM mapping, this might need the extra()
clause,
something like this (100% untested)

  .extra(where=["""
      abs(app_pricelist.width - %s) = (
         SELECT Min(Abs(pl.width - %s))
         FROM app_pricelist pl
         )
      """], params=[w, w])

(that params might need to be
"params=[(w,w)]"...I'm offline
while writing this ATM...but the docs on the extra() call
should
specify how params should be passed)

As you found, this can return multiple items if more than
one are
equally close.  You can do as your code did and arbitrarily
choose the first one by appending "[0]".  Or you
can return the
results and let the user choose.

-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-usersgooglegroups.com
To unsubscribe from this group, send email to
django-users-unsubscribegooglegroups.com
For more options, visit this group at htt
p://groups.google.com/group/django-users?hl=en
-~----------~----~----~----~------~----~------~--~---

On 7/29/07, Tim Chase <django.userstim.thechases.com>
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-usersgooglegroups.com
To unsubscribe from this group, send email to
django-users-unsubscribegooglegroups.com
For more options, visit this group at htt
p://groups.google.com/group/django-users?hl=en
-~----------~----~----~----~------~----~------~--~---

> 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-usersgooglegroups.com
To unsubscribe from this group, send email to
django-users-unsubscribegooglegroups.com
For more options, visit this group at htt
p://groups.google.com/group/django-users?hl=en
-~----------~----~----~----~------~----~------~--~---

Thank you Tim,

Seems to work now with:
w=240
pricelisttable_set.extra(where=["""(table_pri
celisttable.width - %s)
>= ( SELECT Min(Abs(pl.width - %s)) FROM
table_pricelisttable pl)
"""], params=[w, w])[0]


On 7/29/07, Tim Chase <django.userstim.thechases.com>
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-usersgooglegroups.com
To unsubscribe from this group, send email to
django-users-unsubscribegooglegroups.com
For more options, visit this group at htt
p://groups.google.com/group/django-users?hl=en
-~----------~----~----~----~------~----~------~--~---

> Seems to work now with:
> w=240
>
pricelisttable_set.extra(where=["""(table_pri
celisttable.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-usersgooglegroups.com
To unsubscribe from this group, send email to
django-users-unsubscribegooglegroups.com
For more options, visit this group at htt
p://groups.google.com/group/django-users?hl=en
-~----------~----~----~----~------~----~------~--~---

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 <django.userstim.thechases.com>
wrote:
>
> > Seems to work now with:
> > w=240
> >
pricelisttable_set.extra(where=["""(table_pri
celisttable.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-usersgooglegroups.com
To unsubscribe from this group, send email to
django-users-unsubscribegooglegroups.com
For more options, visit this group at htt
p://groups.google.com/group/django-users?hl=en
-~----------~----~----~----~------~----~------~--~---

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 <michael.aucklandgmail.com> 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 <django.userstim.thechases.com> wrote:
> >
> > > Seems to work now with:
> > > w=240
> > >
pricelisttable_set.extra(where=["""(table_pri
celisttable.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-usersgooglegroups.com
To unsubscribe from this group, send email to
django-users-unsubscribegooglegroups.com
For more options, visit this group at htt
p://groups.google.com/group/django-users?hl=en
-~----------~----~----~----~------~----~------~--~---