Find a combination of two elements that have not been viewed together (LINQ, SQL or C #)

Question

I have a page that displays two objects, and then the user selects one of them. I write preference and combination in the MSSQL database and end up storing the data as follows:

UserId=1, BetterObjectId=1, WorseObjectId=2 

Now I would like to no longer show this combination of objects (1.2 / 2.1).

So, how do I create random combinations to show the user, excluding previously viewed combinations?

It seems that this should be a very simple question, but, like most programmers, I can’t sleep and coffee, so your help is much appreciated :-)

The naive approach is very much something like this (and all calls to this function should be wrapped to check to see if the user has already rated as many times as nCr, where n is item count and r is 2):

 public List<Item> GetTwoRandomItems(int userId) { Item i = null, i2 = null; List<Item> r = null; while (i == null || i2 == null) { r = GetTwoRandomItemsRaw(); i = r[0]; i2 = r[1]; if (GetRating(i.Id, i2.Id, userId) != null) /* Checks if viewed */ { i = null; i2 = null; } } return r; } private List<Item> GetTwoRandomItemsRaw() { return Items.ToList().OrderBy(i => Guid.NewGuid()).Take(2).ToList(); } 

edits

Using some SQL, I can generate a list of all elements that are not complete (i.e. there is a combination with an element that the user has not seen), but I do not think this is particularly useful.

I can also imagine how to create any possible combination and exclude already viewed before choosing 2 random elements, but this is another scary decision.

Opportunity (intensive memory for large n) - generate all possible combinations and save the combination in the rating. Then I can just do a SELECT of all the combinations. WHERE combinationId IS NOT IN (SELECT combId FROM ratings WHERE userId = x) with some changes that reflect the symmetrical ratio of the combinations.

Answer 1

 Table Item: ItemId Table Rating: UserId, ItemId1, ItemId2, WinnerId 

If you require ItemId1 <ItemId2 in the rating table, you only need to check the rating table only once.

 var pair = db.Items.Join(db.Items, i1 => i1.ItemId, i2 => i2.ItemId, (i1, i2) => new {i1, i2} ) //produce all pairs .Where(x => x.i1.ItemId < x.i2.ItemId) //filter diagonal to unique pairs .Where(x => !db.Ratings .Where(r => r.UserId == userId && r.ItemId1 == x.i1.ItemId && r.ItemId2 == x.i2.ItemId) .Any() //not any ratings for this user and pair ) .OrderBy(x => db.GetNewId()) //in-database random ordering .First(); // just give me the first one return new List<Item>() {pair.i1, pair.i2 }; 

Here's a blog about getting a "random" translation into a database.

Answer 2

One solution:

 SELECT TOP 1 i.id item1, i2.id item2 from item i, item i2 WHERE i.id <> i2.id AND (SELECT COUNT(*) FROM Rating WHERE userId=@userId AND FK_ItemBetter=i.id AND FK_ItemWorse=i2.id) = 0 AND (SELECT COUNT(*) FROM Rating WHERE userId=@userId AND FK_ItemBetter=i2.id AND FK_ItemWorse=i.id) = 0 ORDER BY NEWID() 

I did not know about the cross join method of simply listing several FROM tables before.

Answer 3

Assuming the list of available items is in the database, I would completely deal with this problem in the database. You already click on the database, no matter why not do it there?

Answer 4

How to place all objects in a queue or on the stack, and then place 2 and 2 until they are empty?