CODEWITHSUNDEEP
neo4jcypher
Jim Evil Chippy Perry
Jim Evil Chippy Perry

Reputation: 51

Cypher - return 1 'random' node from specific relationships

Trying to build a Cypher query that returns ONE entry per 'comic'.

Basic structure is as follows:

(Comic)<-[]-(Media)-[]->(MediaType)

I want to grab 3 'random' comics and return only one Media per comic of said type. As a comic may have 100 medias of the media type, it's a little concerning. I could break this down into multiple queries, but I am sure there's a way for Cypher to do this through its own system.

My original code would do what it needed to, but if there were multiple medias of the same type, one comic would had the possibility of being returned multiple times.

The original Code:

MATCH (mt:MediaType{Name: {mediatype} })
<-[:Is_Media_Of]-(m:Media)-[:Is_Media_For]->(w:Webcomic)
WHERE w.ComicID <> toInt({comicid})
AND m.Status = 'Active'
RETURN m.URL as ImgURL, 
w.Name as ComicName, 
rand() as r 
ORDER BY r ASC Limit toInt({count}) ",
["mediatype"=>$AdType,"count"=>$Count,"comicid"=>$ComicID])

This would sometimes return:

'comic1' 'img'
'comic2' 'img'
'comic1' 'img2'
'comic3' 'img2'
'comic4' 'img1'
'comic3' 'img5'

I'm sure it's pretty straight forward and I'm missing something. Any help is greatly appreciated.

Bruno's Code:

MATCH (mt:MediaType{Name: {mediatype}})
<-[:Is_Media_Of]-(m:Media)-[:Is_Media_For]->
(w:Webcomic) WHERE w.ComicID <> toInt({comicid}) 
AND m.Status = 'Active' 
WITH distinct m, w 
RETURN m.URL as ImgURL, w.Name as ComicName, rand() as r 
ORDER BY r ASC Limit toInt({count})

Result for Bruno's Code:

ImgURL                       ComicName           r
"ms-rot-d1517513337.jpg"    "MoonSlayer"    0.09929832004330252
"ms-rot-b1517513245.jpg"    "MoonSlayer"    0.17924831869113889
"ms-rot-e1517513507.jpg"    "MoonSlayer"    0.23860836768580007
"mrRotator021516985058.jpg" "Mailbox Rocketship"    0.29152315193261946
"rotator1517004270.png" "Grapple Seed"  0.3167179880041201

Current results:

enter image description here

Wanted results: enter image description here

Upvotes: 1

Views: 117

Answers (1)

Bruno Peres
Bruno Peres

Reputation: 16355

Try it:

MATCH (mt:MediaType{Name: {mediatype}})<-[:Is_Media_Of]-(m:Media)-[:Is_Media_For]->(w:Webcomic)
WHERE w.ComicID <> toInt({comicid})
AND m.Status = 'Active'
WITH distinct w, mt, collect(m) as ms
RETURN ms[0].URL as ImgURL, w.Name as ComicName, rand() as r
ORDER BY r ASC Limit toInt({count})

I'm basically grouping by comic and collecting all medias into a list. Then I'm returning the first element of the list.

Upvotes: 1

Related Questions