Filtering Query works not as expected

I use neo4j 5. So it's cypher query language.

goal: Find books for user with genres among which there is at least one preferred genre and no genre that is avoided.

Database: image of database entities and relations

CREATE (:Book {ageLimit: 16,publishedAt: 1999,author: ""Test Author 1"",isbn: ""0000000000001"",storeUrl: ""https://www.test-image.com/some-uri/book1"",imageUrl: ""https://www.test-image.com/some-uri/book1.jpg"",description: ""Test book description 1."",id: ""20c84cdd-50f0-45cf-8269-12daa6a4984b"",title: ""Test Title 1"",lang: ""en""}) ! CREATE (:Book {ageLimit: 18,publishedAt: 2001,author: ""Test Author 2"",isbn: ""0000000000002"",storeUrl: ""https://www.test-image.com/some-uri/book2"",imageUrl: ""https://www.test-image.com/some-uri/book2.jpg"",description: ""Test book description 2."",id: ""75a01e8f-8ab2-4506-a38f-3eef63f3b3cc"",title: ""Test Title 2"",lang: ""en""}) ! CREATE (:Book {ageLimit: 12,publishedAt: 2010,author: ""Test Author 3"",isbn: ""0000000000003"",storeUrl: ""https://www.test-image.com/some-uri/book3"",imageUrl: ""https://www.test-image.com/some-uri/book3.jpg"",description: ""Test book description 3."",id: ""6e15a4e8-12fb-4cf8-8143-c05937fc2f15"",title: ""Test Title 3"",lang: ""en""}) ! CREATE (:Genre {name: ""classical"",id: ""4a85865a-e903-41f3-a6fa-6d912eaac620""}) ! ! CREATE (:Genre {name: ""fantastic"",id: ""dca5f095-6296-4c17-85fa-c6c544eea429""}) ! ! CREATE (:Genre {name: ""action"",id: ""feec627b-7e0f-45f8-81c4-f83b085e7e0c""}) ! ! CREATE (:UserDetails {lastName: ""Dow"",firstName: ""John"",gender: ""MALE"",dateOfBirth: ""1990-01-01"",id: ""075cb495-49b5-42fe-9fec-cef3eb95a49e"",userName: ""default_user"",email: ""default_user@email.com""}) ! ! CREATE (:UserPreferences {userId: ""075cb495-49b5-42fe-9fec-cef3eb95a49e""}) !

How should i change my query to successfully filtering both of parameters (preferred genres and avoided genres)?

I already tried to complete it by query below, but it ignored avoided genres.

Query:


MATCH (u:UserDetails)
                WHERE u.id = $userId
                (u)-[:HAS_PREFERENCES]->(up:UserPreferences{userId: u.id})
                WITH up
                OPTIONAL MATCH (up)-[pref:PREFERRED]->(pg:Genre)
                OPTIONAL MATCH (up)-[avoid:AVOIDED]->(ag:Genre)
                WITH up, collect(DISTINCT pg) AS pg, collect(DISTINCT ag) AS ag
                MATCH (b:Book)-[:BELONGS_TO]->(g:Genre)
                WHERE b.lang = 'en'
                AND (size(ag) = 0 OR NOT any(genre IN ag WHERE genre = g)) //logic which i expected from this line doesn't work
                AND(size(pg) = 0 OR any(genre IN pg WHERE genre = g)) 
                WITH DISTINCT b
                LIMIT 5
                MATCH (b)-[r:BELONGS_TO]->(g:Genre)
                RETURN b, collect(DISTINCT g), collect(r)

Solution

[UPDATED]

My [Original answer] below was very simple, but had the flaw that it ignored outgoing AVOIDED relationships from all relevant Preference nodes.

This query should efficiently and correctly find the distinct books in the genres that the user prefers and does not avoid

MATCH (u:UserDetails)-[:HAS_PREFERENCES]->(prefs)
WHERE u.id = $userId
WITH COLLECT(prefs) AS pList
WITH pList, COLLECT{
  UNWIND pList AS p
  MATCH (p)-[:AVOIDED]->(g)
  RETURN g
} AS avoidedGenres
UNWIND pList AS p
MATCH (p)-[:PREFERRED]->(wantedGenre)
WHERE NOT wantedGenre IN avoidedGenres
MATCH (wantedGenre)<-[:BELONGS_TO]-(book)
RETURN DISTINCT book

[Original answer]

This efficient query finds the distinct books in the genres that the user prefers and does not avoid:

MATCH (u:UserDetails)-[:HAS_PREFERENCES]->(prefs)-[:PREFERRED]->(genre)<-[:BELONGS_TO]-(book)
WHERE u.id = $userId AND NOT (prefs)-[:AVOIDED]->(genre)
RETURN DISTINCT book