CODEWITHSUNDEEP
c#lucene.net
Greg
Greg

Reputation: 3522

Setup Lucene.Net Index

I have a database that lists sports and users and has a join table (UserSports):

Sports Table:
ID   Name
1    Running
2    Swimming
3    Football
4    Baseball
5    Basketball

Users Table:
ID    Name
1     George
2     Jane
3     Alex

UsersSports
UserID   SportID
1         2
3         1
2         4
2         5

I want to search for sports using lucene.net so I create an index for them and make the name analysed. This works great. When I search for "ball" I get football, baseball, basketball back. What I want to do though is for a particular user only return sports where they DON'T have a record in UserSports. So if Jane searched for "ball" it should only return Football. I can do this in SQL using a not in or a left join ... where join is null and that works fine, but I want to add fuzzy logic searching the Lucene.net gives.

What is the best way to index my data in Lucene.Net?

Upvotes: 1

Views: 251

Answers (1)

Jf Beaulac
Jf Beaulac

Reputation: 5246

There are tons of ways to do this.

Since you will never have extremely large amounts of sports, you could simply query the Lucene index like you actually do, and build a SQL query from it:

SELECT *
FROM Sports
WHERE Sports.ID IN([list from lucene])
AND NOT EXISTS(
    SELECT 1
    FROM UsersSports
    WHERE UsersSports.UserId = [current user id]
    AND UserSports.SportID = Sports.ID
)

Upvotes: 1

Related Questions