Relevance in broker queries

Question

Is there any way to obtain the results of a Broker Query with something similar to a relevance value?

I'm doing a query of components with 3 categories, and the query may be a combination of 3 keywords, but if I use a KeywordCriteria with AND, only components with the 3 matching keywords will be obtained. And if I use an OR operator, then components with one, two or three keyword will match, but in this case, is there any way to sort the results by relevance, hence the first results will match 3 keywords, then the following results match 2 keywords and so on?

Answer 1

I suspect that, even if it is possible to formulate a query using the Broker API, then this may be difficult to write, maintain and not very scalable.

Have you considered getting all of the Components using the OR statement and doing some server-side (.NET or JAVA) processing to determine the 'relevance'?
(I have seen something similar done with an addition XML DCP to work with server-side. Although this would be additional templating and work for the Publisher.)

This would probably also make it easier for future changes. For example, if the Categories needed to have a ranking between them (e.g. Components tagged with Category 1 are more important than those tagged with Category 2 AND Category 3) or if certain Keywords were more important.

Answer 2

As Jonathan Williams has already alluded to, it isn't possible to do this directly using the Broker API - you would have to run multiple queries and process the results in code. However, I thought I would share a couple of ideas for achieving this, assuming you don't mind bypassing the API and getting straight into the SQL. (I was going to blog about this in more detail, but as I never have time, this is as good a place as any! Note that the code is based on SQL Server.)

For our examples, let's pretend we have two metadata fields, Fruit_Colour and Fruit_Type.

So, starting with a very basic case where we simply want to say, if Component X has more metadata values (by count) than Component Y, bring back the Components in ranked order. We could do this with the following simple SQL statement:

SELECT ITEM_ID, PUBLICATION_ID, KEY_NAME,
COUNT(KEY_STRING_VALUE) OVER (PARTITION BY ITEM_ID) AS 'COUNT'
FROM CUSTOM_META
WHERE KEY_STRING_VALUE IN ('Yellow', 'Citrus')
AND ITEM_TYPE = 16
ORDER BY COUNT DESC    

The end result is a ranked list of Components, with those having both values being highest in the list. (One could also add TOP() and additional WHERE clauses to narrow down the list).

Now let's extend the example a little. How about we want to say that being a certain colour is more important that being of a certain type (for example, through user input filters). We can achieve this by using the SQL Server RANK() function and a little SQL-fu! In the following code sample, I'm going to use a TABLE variable that will substitute for our user input:

-- Declare table variable for our example
DECLARE @weights TABLE (value NVARCHAR(50), weight INT);    

-- Insert values into table variable for example
INSERT INTO @weights VALUES ('Yellow', 75);
INSERT INTO @weights VALUES ('Citrus', 25);    

-- Create an aliased result set, ranking the rows by weight while joining over the metadata value (KEY_STRING_VALUE)
WITH ranked_content AS (
SELECT ITEM_ID, PUBLICATION_ID, KEY_NAME, KEY_STRING_VALUE, 
  RANK() OVER (PARTITION BY weight ORDER BY ITEM_ID) AS 'RANKING'
FROM CUSTOM_META
LEFT JOIN @weights ON KEY_STRING_VALUE = value
WHERE ITEM_TYPE = 16
)    

-- Select what we want from the result set above and order by ranking
SELECT DISTINCT(ITEM_ID), PUBLICATION_ID, MAX(RANKING) AS 'RANKING'
FROM ranked_content
GROUP BY ITEM_ID, PUBLICATION_ID
ORDER BY RANKING DESC    

This will return a ranked list of Components based on the values and associated weights input.

There are lots ways to do this, depending on your needs (for example, you could change the second example to use KEY_NAME so that you could rank by the presence and weight of a given key, rather than the value), but my intention is to give you some ways to get started. Enjoy!