Re: selecting the 'best' match
Hi! blackwater dev wrote: I have a hold car data such as color, model, make, year, etc. I want to allow the user to answer some questions and I'll present them with the car that 'best' matches their criteria. How do I do this? I still want to return ones that don't match exactly but want the closer matches ordered at the top: Table:cars columns: car_id, make, model, year, color, condition So if the user enterrs: model: Toyota year: 1998 condition:great color: blue I would show them a blue 1998 good conditioned camry first but farther down in the list might still have a blue good condition 98 Honda. Returning records in some specified order requires to sort them, and for this you need some criteria. In most cases, these are values of these records (like a name or size), but this doesn't match your application. You want to order by a distance: How much deviates this record from the perfect match? Mathematically, your records are points in some n-dimensional space (dimensions being model, year, condition, color, probably several others), and your perfect match is one specific such point. If all your dimensions were numerical (like year) or at least ordered (like condition: broken, poor, average, good, great), you could define a distance within each dimension, and then combine these to n-dimensional. Comparison to geometry: When you know the distances in x, y, and z dimension, you can use Pythagoras' formula: dist = sqrt ( (x1 - x)**2 + (y1 - y)**2 + (z1 - z)**2 ) But with non-numerical and even un-ordered data, your choices are pretty arbitrary: What is the distance between colors? Which one is closer to a Toyota - a Ford or a Volkswagen? So I doubt you can define a closeness (or distance) function. In your case, I propose to try UNION: SELECT ... WHERE model = UserModel AND color = UserColor ORDER BY condition, ABS (year - UserYear) UNION SELECT ... WHERE model = UserModel AND color != UserColor ORDER BY condition, ABS (year - UserYear) UNION SELECT ... WHERE model = !UserModel AND color = UserColor ORDER BY condition, ABS (year - UserYear) UNION SELECT ... WHERE model = !UserModel AND color = !UserColor ORDER BY condition, ABS (year - UserYear) This is based on the assumption that the exact match on the model is more important than on the color. If your experience with buyers differs, adapt the order. And if you think a mismatch in both model and color makes the car irrelevant, drop the last SELECT in the UNION. But as soon as the potential buyer says doesn't matter, you would need a different SELECT (one that doesn't differ by that field), so the whole thing calls for a program that generates the whole SQL statement based on the user input. HTH, Jörg -- Joerg Bruehe, MySQL Build Team, joerg.bru...@sun.com (+49 30) 417 01 487 Sun Microsystems GmbH, Komturstraße 18a, D-12099 Berlin Geschaeftsfuehrer: Thomas Schroeder, Wolfgang Engels, Wolf Frenkel Vorsitzender des Aufsichtsrates: Martin Haering Muenchen: HRB161028 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: selecting the 'best' match
you *could* go with if-statements, returning a numerical weight for each criterion if match and 0 if not; summing those and sorting by the sum column. I would do it in code, though - it may or may not be less efficient, but it'll be easier to maintain and read. On Tue, May 12, 2009 at 5:50 PM, blackwater dev blackwater...@gmail.comwrote: I have a hold car data such as color, model, make, year, etc. I want to allow the user to answer some questions and I'll present them with the car that 'best' matches their criteria. How do I do this? I still want to return ones that don't match exactly but want the closer matches ordered at the top: Table:cars columns: car_id, make, model, year, color, condition So if the user enterrs: model: Toyota year: 1998 condition:great color: blue I would show them a blue 1998 good conditioned camry first but farther down in the list might still have a blue good condition 98 Honda. Thanks! -- Celsius is based on water temperature. Fahrenheit is based on alcohol temperature. Ergo, Fahrenheit is better than Celsius. QED.
Re: selecting the 'best' match
blackwater dev wrote: I have a hold car data such as color, model, make, year, etc. I want to allow the user to answer some questions and I'll present them with the car that 'best' matches their criteria. How do I do this? I still want to return ones that don't match exactly but want the closer matches ordered at the top: Table:cars columns: car_id, make, model, year, color, condition So if the user enterrs: model: Toyota year: 1998 condition:great color: blue I would show them a blue 1998 good conditioned camry first but farther down in the list might still have a blue good condition 98 Honda. Thanks! Perhaps: SELECT * FROM cars order by model!='Toyota',model; -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: selecting the 'best' match
Thanks but doing it in code would require me to pull in the entire car table and process it. With potentially tons of rows, seems like I should be able to use the db to get those. On Tue, May 12, 2009 at 12:23 PM, Johan De Meersman vegiv...@tuxera.bewrote: you *could* go with if-statements, returning a numerical weight for each criterion if match and 0 if not; summing those and sorting by the sum column. I would do it in code, though - it may or may not be less efficient, but it'll be easier to maintain and read. On Tue, May 12, 2009 at 5:50 PM, blackwater dev blackwater...@gmail.comwrote: I have a hold car data such as color, model, make, year, etc. I want to allow the user to answer some questions and I'll present them with the car that 'best' matches their criteria. How do I do this? I still want to return ones that don't match exactly but want the closer matches ordered at the top: Table:cars columns: car_id, make, model, year, color, condition So if the user enterrs: model: Toyota year: 1998 condition:great color: blue I would show them a blue 1998 good conditioned camry first but farther down in the list might still have a blue good condition 98 Honda. Thanks! -- Celsius is based on water temperature. Fahrenheit is based on alcohol temperature. Ergo, Fahrenheit is better than Celsius. QED.
Re: selecting the 'best' match
not if you did it in a stored procedure. Given that it's gonna be pretty hard to use indices on this anyway (I think), you're gonna scan the entire table anyway. That's what you get for fuzzy searches. On Tue, May 12, 2009 at 6:55 PM, blackwater dev blackwater...@gmail.comwrote: Thanks but doing it in code would require me to pull in the entire car table and process it. With potentially tons of rows, seems like I should be able to use the db to get those. On Tue, May 12, 2009 at 12:23 PM, Johan De Meersman vegiv...@tuxera.be wrote: you *could* go with if-statements, returning a numerical weight for each criterion if match and 0 if not; summing those and sorting by the sum column. I would do it in code, though - it may or may not be less efficient, but it'll be easier to maintain and read. On Tue, May 12, 2009 at 5:50 PM, blackwater dev blackwater...@gmail.com wrote: I have a hold car data such as color, model, make, year, etc. I want to allow the user to answer some questions and I'll present them with the car that 'best' matches their criteria. How do I do this? I still want to return ones that don't match exactly but want the closer matches ordered at the top: Table:cars columns: car_id, make, model, year, color, condition So if the user enterrs: model: Toyota year: 1998 condition:great color: blue I would show them a blue 1998 good conditioned camry first but farther down in the list might still have a blue good condition 98 Honda. Thanks! -- Celsius is based on water temperature. Fahrenheit is based on alcohol temperature. Ergo, Fahrenheit is better than Celsius. QED. -- Celsius is based on water temperature. Fahrenheit is based on alcohol temperature. Ergo, Fahrenheit is better than Celsius. QED.
Re: selecting the 'best' match
in your code, you can define ranges of say if the model year being looked for is 2002, then present model years 2000 thru 2004. --Curtis blackwater dev wrote: Thanks but doing it in code would require me to pull in the entire car table and process it. With potentially tons of rows, seems like I should be able to use the db to get those. On Tue, May 12, 2009 at 12:23 PM, Johan De Meersman vegiv...@tuxera.bewrote: you *could* go with if-statements, returning a numerical weight for each criterion if match and 0 if not; summing those and sorting by the sum column. I would do it in code, though - it may or may not be less efficient, but it'll be easier to maintain and read. On Tue, May 12, 2009 at 5:50 PM, blackwater dev blackwater...@gmail.comwrote: I have a hold car data such as color, model, make, year, etc. I want to allow the user to answer some questions and I'll present them with the car that 'best' matches their criteria. How do I do this? I still want to return ones that don't match exactly but want the closer matches ordered at the top: Table:cars columns: car_id, make, model, year, color, condition So if the user enterrs: model: Toyota year: 1998 condition:great color: blue I would show them a blue 1998 good conditioned camry first but farther down in the list might still have a blue good condition 98 Honda. Thanks! -- Celsius is based on water temperature. Fahrenheit is based on alcohol temperature. Ergo, Fahrenheit is better than Celsius. QED.
