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'
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'
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'
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!