Filtering Query with logic not works as expected

Neo4j Graph Platform Cypher
, ,
1

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

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

Data
image
Blue node - UserDetails
Red node - UserPreferences
Purple node - Genre
Yellow node - Book

Here, user User has preferences
Preferences node related to genres with AVOIDED relation which means that user don't want to see books with related genre and PREFERRED which means that user want to see books with at leas one related genre.

Cypher Script to populate the database:

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)
2

This should find all books that have a genre the user prefers, but also does not have a genre the user wants to avoid.

MATCH (u:UserDetails{id: $userId})
MATCH (u)-[:HAS_PREFERENCES]->(p:UserPreferences)-[:PREFERRED]->(:Genre)<-[:BELONGS_TO]-(b:Book)
WHERE NOT EXISTS( (b)-[:BELONGS_TO]->(:Genre)<-[:AVOIDED]-(p) )
RETURN DISTINCT b
3

Thank you so much! It works. Do you know how to make this query sustainable when avoided or/and preferred genres is not set? If it not set, query should return books ignoring filtration.

4

Can you provide an example, as your diagram has all books related to a user through a genre node and a user preference node?

5

sorry, but i can't do it now. You just could imagine that other genres exist.
For example:

CREATE (:Genre{name: "Fiction"})
CREATE (:Genre{name: "Novel"})
CREATE (:Book{title: "New title"})

MATCH (g:Genre{name: "Fiction"})
MATCH (b:Book{title: "New title"})
MERGE (b)-[:BELONGS_TO]->(g)
6

Sorry...I noticed some syntax errors in the query. I modified the post directly to fix them.

Still not sure what you are looking for. The following is a query that finds all books with genres that they prefer, without considering if the book is assigned to another genre that the user does not prefer. I just removed the 'where' clause to filter out those genres that are to be avoided.

MATCH (u:UserDetails{id: $userId})
MATCH (u)-[:HAS_PREFERENCES]->(p:UserPreferences)-[:PREFERRED]->(:Genre)<-[:BELONGS_TO]-(b:Book)
RETURN DISTINCT b

The following finds all books that lender is associated with:

MATCH (u:UserDetails{id: $userId})
MATCH (u)-[:HAS_PREFERENCES]->(p:UserPreferences)-[]->(:Genre)<-[:BELONGS_TO]-(b:Book)
RETURN DISTINCT b
7 
Try this:
match (f:UserDetails)-[]-(a:UserPreferences)-[r]-(b:Genre)-[]-(c:Book)
with (f.lastName + "," + f.firstName) as user, a, b, c, r
with distinct user, type(r) as rel, collect(distinct c.title) as titles
return  user, rel, size(titles) as cnt, titles

Result:

Screenshot 2024-06-19 at 4.17.22 PM
Screenshot 2024-06-19 at 4.17.22 PM890×162 32.8 KB