neo4j - 在不同的属性上使用 neo4j DISTINCT 和 ORDER BY

Question

我正在尝试在 neo4j 版本 1.9M02 上运行以下密码查询

START me=node(2)
            MATCH me-[:FOLLOWS]->friends
            ,friends-[fr:ADDED|STOCKS]->products
            ,me-[r?]->products
            WHERE r is null
            RETURN products._id
            ORDER BY fr.CreatedOn DESC
            SKIP 0
            LIMIT 10

我希望得到所有由我的 friend 添加或库存但我没有添加或库存的产品，按关系创建时间排序。此查询返回正确的结果，但具有重复的 products._id 值(一个用户添加了该产品，另一个用户 STOCKED 它)。但我只需要这个 products._id 的一个实例所以我试过了

START me=node(2)
            MATCH me-[:FOLLOWS]->friends
            ,friends-[fr:ADDED|STOCKS]->products
            ,me-[r?]->products
            WHERE r is null
            RETURN DISTINCT products._id
            ORDER BY fr.CreatedOn DESC
            SKIP 0
            LIMIT 10

(为 products._id 添加了 DISTINCT)但是这次我得到了 Unknown identifier fr 错误。所以我在 RETURN 语句中添加了 fr

START me=node(2)
            MATCH me-[:FOLLOWS]->friends
            ,friends-[fr:ADDED|STOCKS]->products
            ,me-[r?]->products
            WHERE r is null
            RETURN DISTINCT products._id,fr
            ORDER BY fr.CreatedOn DESC
            SKIP 0
            LIMIT 10

此查询没有任何错误，但会像以前一样返回重复的产品 ID。

我最近几天只玩过 neo4j，所以根本不是专家。如果有人能在这里帮助我，我将不胜感激。

Answer 1

我创建了一个 little case与 console.neo4j.org

通过以下查询，我返回 [4, 3, 2, 4, 3, 2, 4, 3 ,2] :

START me=node(1) 
MATCH me-[:FOLLOWS]->friends
,friends-[fr:ADDED|STOCKS]->products
,me-[r?]->products 
WHERE r is null 
with distinct products._id as id, fr.CreatedOn as CreatedOn 
ORDER BY CreatedOn DESC 
RETURN id

在最后一行加上 distinct，我回来 [3, 4, 2] :

START me=node(1) 
MATCH me-[:FOLLOWS]->friends
,friends-[fr:ADDED|STOCKS]->products
,me-[r?]->products 
WHERE r is null 
with distinct products._id as id, fr.CreatedOn as CreatedOn 
ORDER BY CreatedOn DESC 
RETURN distinct id

看起来排序会以不同的方式丢失。

编辑

备案后 an issue on github在这一点上，我花了很长时间才收到 answer .

答案中的建议是运行此查询:

START me=node(1) 
MATCH me-[:FOLLOWS]->friends-[fr:ADDED|STOCKS]->product, 
WHERE not(me-->product)
RETURN product._id as id, min(fr.CreatedOn) as CreatedOn 
ORDER BY CreatedOn DESC 

我修改了查询，使其只返回 ID:

START me=node(1) 
MATCH me-[:FOLLOWS]->friends-[fr:ADDED|STOCKS]->product 
WHERE not(me-->product) 
WITH product._id as id, min(fr.CreatedOn) as CreatedOn 
ORDER BY CreatedOn DESC 
RETURN id

瞧，它返回 [4, 3, 2] ! (我还创建了一个 updated test case 。)