Neo4j 5 Cypher查询优化：实现偏好与规避书籍类型双重过滤

需求说明 #

筛选满足以下条件的英文书籍：

• 书籍包含的类型中至少有一种是用户偏好类型
• 书籍不包含任何用户规避类型

原查询未能正确过滤用户规避的类型，需调整实现双重条件的有效过滤。

数据库初始化语句 #

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"})

原查询语句 #

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)) // 此处逻辑未生效
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)

修正后的查询语句 #

MATCH (u:UserDetails {id: $userId})-[:HAS_PREFERENCES]->(up:UserPreferences)
OPTIONAL MATCH (up)-[:PREFERRED]->(pg:Genre)
OPTIONAL MATCH (up)-[:AVOIDED]->(ag:Genre)
// 收集偏好和规避的类型名称
WITH collect(DISTINCT pg.name) AS preferredGenres, collect(DISTINCT ag.name) AS avoidedGenres
// 匹配英文书籍及其所有类型
MATCH (b:Book {lang: 'en'})-[:BELONGS_TO]->(g:Genre)
WITH b, collect(DISTINCT g.name) AS bookGenres, preferredGenres, avoidedGenres
WHERE 
  // 书籍至少包含一种偏好类型（无偏好时跳过此条件）
  (size(preferredGenres) = 0 OR any(gName IN preferredGenres WHERE gName IN bookGenres))
  AND 
  // 书籍完全不包含任何规避类型
  (size(avoidedGenres) = 0 OR none(gName IN avoidedGenres WHERE gName IN bookGenres))
// 去重后取前5本
WITH DISTINCT b LIMIT 5
// 关联书籍的类型和关系返回
MATCH (b)-[r:BELONGS_TO]->(g:Genre)
RETURN b, collect(DISTINCT g), collect(r)

关键修改说明 #

• 整体校验书籍类型：原查询逐一对单个类型判断，导致只要有一个类型符合偏好，即使存在规避类型的书籍也会被保留。现在先收集书籍的所有类型，再整体校验是否符合条件。
• 改用类型名称比对：直接比较节点可能因引用问题导致判断失效，使用Genre的name属性进行比对更可靠稳定。
• 优化条件逻辑：用none()明确判断“无规避类型”，逻辑更直观；同时确保先聚合书籍所有类型再做校验，避免部分类型通过但整体不符合的情况。

内容的提问来源于stack exchange，提问作者gexter