Skip to content

Instantly share code, notes, and snippets.

@rvanbruggen
Last active March 6, 2016 20:49
Show Gist options
  • Save rvanbruggen/1a23584083f7264699ef to your computer and use it in GitHub Desktop.
Save rvanbruggen/1a23584083f7264699ef to your computer and use it in GitHub Desktop.
Qcon 2016 graph
create index on :Company(name);
create index on :Day(date);
create index on :Time(time)
create index on :Room(name);
create index on :Session(title);
create index on :Track(name);
//ALL IN ONE QUERY
load csv with headers from "https://docs.google.com/spreadsheets/d/1aFhc5zcxCEEPnS0FWmlcltcaYX6wCqhhbtsoZiRGu3A/export?format=csv&id=1aFhc5zcxCEEPnS0FWmlcltcaYX6wCqhhbtsoZiRGu3A&gid=1550249063" as hosts
merge (p:Person {name: hosts.Name, title: hosts.Title})
merge (c:Company {name: hosts.Company})
merge (p)-[:WORKS_FOR]->(c)
with hosts
load csv with headers from "https://docs.google.com/spreadsheets/d/1aFhc5zcxCEEPnS0FWmlcltcaYX6wCqhhbtsoZiRGu3A/export?format=csv&id=1aFhc5zcxCEEPnS0FWmlcltcaYX6wCqhhbtsoZiRGu3A&gid=0" as csv
merge (d:Day {date: toInt(csv.day)})
with csv
match (d:Day), (d2:Day)
where d.date = d2.date-1
merge (d)-[:PRECEDES]-(d2)
with csv
merge (r:Room {name: csv.room})
merge (t:Track {name: csv.track})
merge (p:Person {name: csv.speaker, title: csv.title})
merge (c:Company {name: csv.company})
merge (p)-[:WORKS_FOR]->(c)
with csv
match (d:Day {date: toInt(csv.day)})
merge (t1:Time {time: toInt(csv.starttime)})-[:PART_OF]->(d)
merge (t2:Time {time: toInt(csv.endtime)})-[:PART_OF]->(d)
with csv
match (t2:Time {time: toInt(csv.endtime)})-[:PART_OF]->(d:Day {date: toInt(csv.day)})<-[:PART_OF]-(t1:Time {time: toInt(csv.starttime)}), (r:Room {name: csv.room}), (t:Track {name: csv.track}), (p:Person {name: csv.speaker, title: csv.title}), (h:Person {name: csv.host})
merge (s:Session {title: csv.talk})
merge (s)<-[:SPEAKS_IN]-(p)
merge (s)-[:IN_ROOM]->(r)
merge (s)-[:STARTS_AT]->(t1)
merge (s)-[:ENDS_AT]->(t2)
merge (s)-[:IN_TRACK]->(t)
merge (h)-[:HOSTS]->(t);
create index on :Company(name);
create index on :Day(date);
create index on :Time(time)
create index on :Room(name);
create index on :Session(title);
create index on :Track(name);
schema await;
// HOSTS: https://docs.google.com/spreadsheets/d/1aFhc5zcxCEEPnS0FWmlcltcaYX6wCqhhbtsoZiRGu3A/export?format=csv&id=1aFhc5zcxCEEPnS0FWmlcltcaYX6wCqhhbtsoZiRGu3A&gid=1550249063
//add the hosts
load csv with headers from "https://docs.google.com/spreadsheets/d/1aFhc5zcxCEEPnS0FWmlcltcaYX6wCqhhbtsoZiRGu3A/export?format=csv&id=1aFhc5zcxCEEPnS0FWmlcltcaYX6wCqhhbtsoZiRGu3A&gid=1550249063" as csv
merge (p:Person {name: csv.Name, title: csv.Title})
merge (c:Company {name: csv.Company})
merge (p)-[:WORKS_FOR]->(c);
// schedule
https://docs.google.com/spreadsheets/d/1aFhc5zcxCEEPnS0FWmlcltcaYX6wCqhhbtsoZiRGu3A/export?format=csv&id=1aFhc5zcxCEEPnS0FWmlcltcaYX6wCqhhbtsoZiRGu3A&gid=0
//add the days
load csv with headers from "https://docs.google.com/spreadsheets/d/1aFhc5zcxCEEPnS0FWmlcltcaYX6wCqhhbtsoZiRGu3A/export?format=csv&id=1aFhc5zcxCEEPnS0FWmlcltcaYX6wCqhhbtsoZiRGu3A&gid=0" as csv
merge (d:Day {date: toInt(csv.day)});
//connect the days
match (d:Day), (d2:Day)
where d.date = d2.date-1
merge (d)-[:PRECEDES]-(d2);
//add the rooms, traxcks, speakers and speaker's companies
load csv with headers from "https://docs.google.com/spreadsheets/d/1aFhc5zcxCEEPnS0FWmlcltcaYX6wCqhhbtsoZiRGu3A/export?format=csv&id=1aFhc5zcxCEEPnS0FWmlcltcaYX6wCqhhbtsoZiRGu3A&gid=0" as csv
merge (r:Room {name: csv.room})
merge (t:Track {name: csv.track})
merge (p:Person {name: csv.speaker, title: csv.title})
merge (c:Company {name: csv.company})
merge (p)-[:WORKS_FOR]->(c);
//add the timeslots to each day
load csv with headers from "https://docs.google.com/spreadsheets/d/1aFhc5zcxCEEPnS0FWmlcltcaYX6wCqhhbtsoZiRGu3A/export?format=csv&id=1aFhc5zcxCEEPnS0FWmlcltcaYX6wCqhhbtsoZiRGu3A&gid=0" as csv
match (d:Day {date: toInt(csv.day)})
merge (t1:Time {time: toInt(csv.starttime)})-[:PART_OF]->(d)
merge (t2:Time {time: toInt(csv.endtime)})-[:PART_OF]->(d);
//add the sessions and connect them up
load csv with headers from "https://docs.google.com/spreadsheets/d/1aFhc5zcxCEEPnS0FWmlcltcaYX6wCqhhbtsoZiRGu3A/export?format=csv&id=1aFhc5zcxCEEPnS0FWmlcltcaYX6wCqhhbtsoZiRGu3A&gid=0" as csv
match (t2:Time {time: toInt(csv.endtime)})-[:PART_OF]->(d:Day {date: toInt(csv.day)})<-[:PART_OF]-(t1:Time {time: toInt(csv.starttime)}), (r:Room {name: csv.room}), (t:Track {name: csv.track}), (p:Person {name: csv.speaker, title: csv.title}), (h:Person {name: csv.host})
merge (s:Session {title: csv.talk})
merge (s)<-[:SPEAKS_IN]-(p)
merge (s)-[:IN_ROOM]->(r)
merge (s)-[:STARTS_AT]->(t1)
merge (s)-[:ENDS_AT]->(t2)
merge (s)-[:IN_TRACK]->(t)
merge (h)-[:HOSTS]->(t);
//Querying the Qcon Schedule
//Look at day 1
match (d:Day {date:20160307})<--(t:Time)<--(s:Session)--(connections)
return d,t,s,connections
limit 50
//Look at two people
match (p1:Person), (p2:Person),
path = allshortestpaths( (p1)-[*]-(p2) )
where p1.name contains "Hunger"
and p2.name contains "Webber"
return path
//Look at a person and a company
match (c:Company {name:"ThoughtWorks"}), (p:Person {name:"Jim Webber"}),
path = allshortestpaths( (c)-[*]-(p) )
return path
//Look at sessions with more than one speaker
match (s:Session)-[r:SPEAKS_IN]-(p:Person)
with s, collect(p) as person, count(p) as count
where count > 1
return s,person
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment