Ranked Search

[jagould2000]

First, the structure. I have a table that contains two fields, id and attribute. id is the id of a project and attribute is an attribute the project has. Projects can have multiple attributes, so there are multiple records in the table with the same id.

 

I have build a search that works the following way:

 

1. A table is created of project id's that matched the search criteria: "CREATE TABLE temp SELECT id FROM projectprofiles WHERE attribute LIKE 'first search item' OR attribute LIKE 'second search item'....

 

Example, project number 8 had 4 matches, project number 2 had 1 match:

 

8

8

2

8

8

 

2. From that table, I rank them in a new table - most matches to least: CREATE TABLE temp2 SELECT tA.* FROM temp AS tA JOIN (SELECT id, COUNT(*) AS occur FROM temp GROUP BY id) AS dtA ON ( tA.id = dtA.id ) ORDER BY dtA.occur DESC

 

Output from example above:

 

8

8

8

8

2

 

3. I now have a table of id's, temp2, listed in ranked order, but I only want to display them once each, so I query this table: SELECT * FROM temp2 GROUP BY id

 

Example from above produces a ranked list of:

 

8

2

 

4. I drop tables temp and temp2

 

 

This all works - but I need some help with a few questions:

 

1. Is creating 2 temp tables per search a problem for server efficiency, etc. (I already use random names to avoid conflicts.) Is there a way to use subqueries or nested queries to achieve this all in one step without creating a table?

2. I would like to know a "rank" number for each record in the last step - can the second query that ranks them record the count into a new "count" column?

 

My MySQL skills are pretty basic and I've managed to piece this together from examples I found on the web - but I'm a little lost when it comes to modifying/customizing them - especially the ranking query.

 

Thanks in advance for all your help.

 

Jonathan

 

 

[Barand]

SELECT id

FROM attributes

WHERE attribute IN ('first search item', 'second search item')

GROUP BY id

ORDER BY COUNT(attribute) DESC

[jagould2000]

Excellent...

 

I added COUNT(id) on the SELECT and it gave me the ranking in the result also.

 

One last question. Is it possible to introduce some kind of weighting to the order?

 

SELECT id, COUNT( id )

FROM projectprofiles

WHERE attribute

IN (

'Alabama',  'North America'

)

GROUP BY id

 

ORDER BY (COUNT( id ) * different weight passed for each type) DESC

LIMIT 0 , 30

 

So, for example, the attributes also have a column 'type'. So if I search for 'Alabama' and 'North America right now on this table using your previous query:

 

 

id      |    attribute    |    type

 

8            Alabama          state

8            North America  continent

9            Alabama          state

 

 

I get this result

 

id      |    COUNT(id)

 

8            2

9            1

 

I would like to be able to pass some kind of weighting like state = 5 and continent = 1 and get this result:

 

id      |    COUNT(id)

 

8            6

9            5

 

 

Possible?

 

Thanks for your help... your first response greatly simplified my code. It would be great to be able to weight the search though...

 

Jonathan

[Barand]

try

SELECT id, SUM(CASE `type` WHEN 'state' THEN 5 WHEN 'continent' THEN 1 END) as total
FROM projectprofiles
WHERE attribute
IN (
'Alabama',  'North America'
)
GROUP BY id

ORDER BY total DESC
LIMIT 0 , 30

[jagould2000]

You rock. Works perfectly. Mark another one as solved.

 

Jonathan

 

Someone needs to come up with a website that lets you buy a guy a beer via email . I submit your email and my paypal account, they send you an email with a coupon for beer or ship a cold one to you in dry ice.

[Barand]

We use beer coupons at my local. They have a "5" or a "10" in one corner and the Queen's head on them 

[cooldude832]

13 thousand post with avg 5 per solving a question  = 2600 beers = very drunk barand

[Barand]

That is over 5 years (and 5 days), so it's less than 1.5 beers per day. I think my liver can handle that.