Cypher Query Language: 45s SQL to 12ms in Neo4j

// io.thecodeforge: Node syntax — (variable:Label {key: value})
// This creates a node with label 'Person' and two properties
CREATE (alice:Person {name: 'Alice', age: 32, city: 'London'})
RETURN alice;

// io.thecodeforge: Relationship syntax — -[variable:TYPE {key: value}]->
// This creates a directed relationship with a property
CREATE (bob:Person {name: 'Bob', age: 28, city: 'Paris'})
CREATE (alice)-[r:FRIEND {since: date('2019-06-15'), context: 'university'}]->(bob)
RETURN alice, r, bob;

// io.thecodeforge: Pattern — the fundamental unit of Cypher queries
// This pattern matches any Person who is FRIENDs with any other Person
MATCH (p1:Person)-[:FRIEND]->(p2:Person)
RETURN p1.name AS person, p2.name AS friend;

// io.thecodeforge: Anonymous nodes — when you don't need to reference them
// Finds all people Alice is friends with, without naming the relationship
MATCH (alice:Person {name: 'Alice'})-[:FRIEND]->(friend)
RETURN friend.name, friend.city;

// io.thecodeforge: Multiple labels — a node can have more than one
CREATE (charlie:Person:Developer {name: 'Charlie', language: 'Java'})
RETURN charlie;

// io.thecodeforge: Matching multiple labels — find all Developer-People
MATCH (dev:Person:Developer)
RETURN dev.name, dev.language;

// io.thecodeforge: Create a single node with properties
CREATE (order:Order {
  order_id: 'ORD-2024-001',
  total: 149.99,
  currency: 'GBP',
  status: 'pending',
  created_at: datetime()
})
RETURN order;

// io.thecodeforge: Create nodes and a relationship in one statement
// This is the most common production pattern — create entity + link to existing
CREATE (user:User {name: 'Dana', email: 'dana@thecodeforge.io'})
WITH user
MATCH (org:Organization {name: 'TheCodeForge'})
CREATE (user)-[rel:MEMBER_OF {role: 'engineer', joined: date('2024-01-15')}]->(org)
RETURN user.name, rel.role, org.name;

// io.thecodeforge: Create multiple nodes at once
CREATE
  (london:City {name: 'London', country: 'UK', population: 8982000}),
  (paris:City {name: 'Paris', country: 'France', population: 2161000}),
  (berlin:City {name: 'Berlin', country: 'Germany', population: 3645000});

// io.thecodeforge: Create an entire subgraph in one statement
// Product with variants, categories, and supplier — all linked
CREATE (p:Product {sku: 'WIDGET-PRO', name: 'Widget Pro', price: 29.99})
CREATE (cat:Category {name: 'Electronics'})
CREATE (sup:Supplier {name: 'AcmeCorp', country: 'China'})
CREATE (p)-[:IN_CATEGORY]->(cat)
CREATE (p)-[:SUPPLIED_BY {lead_time_days: 14}]->(sup)
RETURN p.name, cat.name, sup.name;

// io.thecodeforge: Create a relationship between EXISTING nodes
// This is how you link entities after initial creation
MATCH (alice:Person {name: 'Alice'})
MATCH (bob:Person {name: 'Bob'})
CREATE (alice)-[:FRIEND {since: date('2023-03-10')}]->(bob)
RETURN 'Friendship created' AS result;

// io.thecodeforge: Basic MATCH — find all developers
MATCH (dev:Developer)
RETURN dev.name, dev.language, dev.years_experience;

// io.thecodeforge: MATCH with WHERE — filtering on properties
MATCH (dev:Developer)
WHERE dev.language = 'Java' AND dev.years_experience >= 5
RETURN dev.name AS senior_java_dev;

// io.thecodeforge: Inline property matching — shorthand for simple WHERE
// Equivalent to the query above but more concise
MATCH (dev:Developer {language: 'Java'})
WHERE dev.years_experience >= 5
RETURN dev.name AS senior_java_dev;

// io.thecodeforge: String matching — STARTS WITH, ENDS WITH, CONTAINS
MATCH (user:User)
WHERE user.email ENDS WITH '@thecodeforge.io'
  AND user.name STARTS WITH 'A'
RETURN user.name, user.email;

// io.thecodeforge: IN operator — matching against a list
MATCH (dev:Developer)
WHERE dev.language IN ['Java', 'Kotlin', 'Scala']
RETURN dev.name, dev.language
ORDER BY dev.language;

// io.thecodeforge: IS NULL / IS NOT NULL — checking for missing properties
MATCH (order:Order)
WHERE order.shipped_at IS NULL
  AND order.status = 'confirmed'
RETURN order.order_id, order.total
ORDER BY order.created_at DESC
LIMIT 10;

// io.thecodeforge: Regular expression matching
MATCH (product:Product)
WHERE product.sku =~ 'WIDGET-.*-PRO'
RETURN product.name, product.sku;

// io.thecodeforge: OPTIONAL MATCH — like a LEFT JOIN in SQL
// Returns users even if they have no orders
MATCH (user:User)
OPTIONAL MATCH (user)-[:PLACED]->(order:Order)
RETURN user.name,
       count(order) AS order_count,
       CASE WHEN order IS NULL THEN 'no orders' ELSE 'has orders' END AS status;

// io.thecodeforge: Multiple MATCH clauses — chained traversal
// Find developers who work on projects in a specific category
MATCH (dev:Developer)-[:CONTRIBUTES_TO]->(proj:Project)
MATCH (proj)-[:IN_CATEGORY]->(cat:Category {name: 'Backend'})
WHERE dev.years_experience >= 3
RETURN dev.name, proj.name, cat.name
ORDER BY dev.years_experience DESC;

// io.thecodeforge: RETURN with aliases and computed values
MATCH (dev:Developer)
RETURN dev.name AS developer,
       dev.language AS primary_language,
       dev.years_experience AS experience,
       CASE
         WHEN dev.years_experience >= 10 THEN 'Principal'
         WHEN dev.years_experience >= 5 THEN 'Senior'
         WHEN dev.years_experience >= 2 THEN 'Mid'
         ELSE 'Junior'
       END AS level
ORDER BY dev.years_experience DESC;

// io.thecodeforge: DISTINCT — remove duplicate results
MATCH (dev:Developer)-[:CONTRIBUTES_TO]->(proj:Project)
RETURN DISTINCT dev.name AS developer
ORDER BY dev.name;

// io.thecodeforge: Pagination with SKIP and LIMIT
// Page 1: first 10 results
MATCH (user:User)
RETURN user.name, user.email, user.created_at
ORDER BY user.created_at DESC
LIMIT 10;

// Page 2: skip first 10, return next 10
MATCH (user:User)
RETURN user.name, user.email, user.created_at
ORDER BY user.created_at DESC
SKIP 10 LIMIT 10;

// io.thecodeforge: RETURN relationships and their properties
MATCH (a:Person)-[r:FRIEND]->(b:Person)
RETURN a.name AS person,
       b.name AS friend,
       r.since AS friends_since,
       duration.between(r.since, date()).years AS years_of_friendship
ORDER BY r.since ASC;

// io.thecodeforge: Coalesce — provide defaults for missing properties
MATCH (product:Product)
RETURN product.name,
       coalesce(product.description, 'No description available') AS description,
       coalesce(product.rating, 0) AS rating
ORDER BY product.rating DESC;

// io.thecodeforge: SET — update a single property
MATCH (user:User {email: 'alice@thecodeforge.io'})
SET user.last_login = datetime()
RETURN user.name, user.last_login;

// io.thecodeforge: SET — update multiple properties at once
MATCH (order:Order {order_id: 'ORD-2024-001'})
SET order.status = 'shipped',
    order.shipped_at = datetime(),
    order.tracking_number = 'TRACK-99821'
RETURN order.order_id, order.status, order.tracking_number;

// io.thecodeforge: SET — update from a map (common in REST API handlers)
// The map comes from the request body in production
WITH {status: 'delivered', delivered_at: '2024-03-15T14:30:00Z'} AS updates
MATCH (order:Order {order_id: 'ORD-2024-001'})
SET order += updates  // += merges the map into existing properties
RETURN order.order_id, order.status, order.delivered_at;

// io.thecodeforge: SET — add a label to an existing node
MATCH (user:User {email: 'alice@thecodeforge.io'})
SET user:Admin
RETURN labels(user) AS labels;  // ['User', 'Admin']

// io.thecodeforge: REMOVE — delete a property
MATCH (user:User {email: 'alice@thecodeforge.io'})
REMOVE user.temporary_token
RETURN user.name, user.temporary_token;  // null — property is gone

// io.thecodeforge: REMOVE — remove a label from a node
MATCH (user:User {email: 'alice@thecodeforge.io'})
REMOVE user:Admin
RETURN labels(user) AS labels;  // ['User'] — Admin label removed

// io.thecodeforge: Production pattern — partial update with coalesce
// Preserve existing rating if the update doesn't include one
MATCH (product:Product {sku: 'WIDGET-PRO'})
SET product.name = coalesce($update_name, product.name),
    product.price = coalesce($update_price, product.price),
    product.updated_at = datetime()
RETURN product.name, product.price;

// io.thecodeforge: DELETE — remove a relationship only
// The nodes remain; only the connection is severed
MATCH (alice:Person {name: 'Alice'})-[r:FRIEND]->(bob:Person {name: 'Bob'})
DELETE r
RETURN 'Friendship removed' AS result;

// io.thecodeforge: DETACH DELETE — remove a node and ALL its relationships
// Use this when deleting an entity entirely
MATCH (user:User {email: 'spam@bot.net'})
DETACH DELETE user;

// io.thecodeforge: Soft delete — production pattern
// Mark as deleted instead of removing; preserves graph integrity
MATCH (order:Order {order_id: 'ORD-2024-999'})
SET order.deleted = true,
    order.deleted_at = datetime(),
    order.deleted_by = 'admin@thecodeforge.io'
RETURN order.order_id, order.deleted;

// io.thecodeforge: Filtering out soft-deleted nodes in queries
// Every MATCH in your app should include this filter
MATCH (order:Order)
WHERE order.deleted IS NULL
RETURN order.order_id, order.total
ORDER BY order.created_at DESC
LIMIT 20;

// io.thecodeforge: Delete all relationships of a specific type
// Useful for bulk cleanup during data migrations
MATCH ()-[r:TEMPORARY_LINK]->()
DELETE r
RETURN count(r) AS deleted_count;

// io.thecodeforge: Conditional delete — only delete if certain conditions met
MATCH (session:Session)
WHERE session.last_active < datetime() - duration('P30D')
  AND session.active = false
DETACH DELETE session
RETURN count(session) AS expired_sessions_deleted;

// io.thecodeforge: MERGE — find or create a node
// First run: creates the node. Second run: finds it.
MERGE (p:Project {name: 'ForgeAPI'})
ON CREATE SET p.created_at = datetime(),
              p.version = '1.0',
              p.status = 'active'
ON MATCH SET p.last_accessed = datetime()
RETURN p.name, p.version, p.status;

// io.thecodeforge: MERGE — idempotent relationship creation
// Prevents duplicate FRIEND relationships between the same two people
MATCH (alice:Person {name: 'Alice'})
MATCH (bob:Person {name: 'Bob'})
MERGE (alice)-[r:FRIEND]->(bob)
ON CREATE SET r.since = date(), r.source = 'manual'
RETURN alice.name, type(r) AS relationship, bob.name, r.since;

// io.thecodeforge: MERGE with unique constraint — production pattern
// Requires: CREATE CONSTRAINT unique_email FOR (u:User) REQUIRE u.email IS UNIQUE
MERGE (user:User {email: 'dana@thecodeforge.io'})
ON CREATE SET user.name = 'Dana',
              user.created_at = datetime(),
              user.signup_source = 'organic'
ON MATCH SET user.last_login = datetime()
RETURN user.name, user.email, user.created_at;

// io.thecodeforge: MERGE on a relationship path — create if not exists
// This is the upsert pattern for connection-based data
MATCH (product:Product {sku: 'WIDGET-PRO'})
MATCH (category:Category {name: 'Electronics'})
MERGE (product)-[r:IN_CATEGORY]->(category)
ON CREATE SET r.assigned_at = datetime(),
              r.assigned_by = 'system'
RETURN product.name, type(r), category.name;

// io.thecodeforge: MERGE for ETL — batch upsert from external data
// Load a list of users and upsert each one
UNWIND [
  {email: 'alice@thecodeforge.io', name: 'Alice', score: 95},
  {email: 'bob@thecodeforge.io', name: 'Bob', score: 87},
  {email: 'newuser@thecodeforge.io', name: 'Eve', score: 92}
] AS row
MERGE (u:User {email: row.email})
ON CREATE SET u.name = row.name,
              u.score = row.score,
              u.first_seen = datetime()
ON MATCH SET u.score = row.score,
             u.last_updated = datetime()
RETURN u.email, u.name, u.score;

// io.thecodeforge: WITH for aggregation — find prolific contributors
// Count projects per developer, then filter on the count
MATCH (dev:Developer)-[:CONTRIBUTES_TO]->(proj:Project)
WITH dev, count(proj) AS project_count
WHERE project_count >= 3
RETURN dev.name AS developer, project_count
ORDER BY project_count DESC;

// io.thecodeforge: WITH for query chaining — multi-stage filtering
// Stage 1: Find active projects
// Stage 2: Find developers on those projects
// Stage 3: Aggregate and filter
MATCH (proj:Project {status: 'active'})
WITH proj
MATCH (dev:Developer)-[:CONTRIBUTES_TO]->(proj)
WITH dev, collect(proj.name) AS projects, count(proj) AS num_projects
WHERE num_projects >= 2
RETURN dev.name, projects, num_projects
ORDER BY num_projects DESC;

// io.thecodeforge: WITH with LIMIT — process in batches
// Find the 100 most recent users, then do expensive operations on them
MATCH (user:User)
WITH user
ORDER BY user.created_at DESC
LIMIT 100
MATCH (user)-[:PLACED]->(order:Order)
WITH user, count(order) AS order_count, sum(order.total) AS total_spent
WHERE total_spent > 500
RETURN user.name, user.email, order_count, total_spent
ORDER BY total_spent DESC;

// io.thecodeforge: WITH as a scope boundary — only pass what you need
MATCH (dev:Developer)-[:CONTRIBUTES_TO]->(proj:Project)
WITH dev.name AS name, dev.language AS lang, count(proj) AS projects
// dev is no longer available here — only name, lang, projects
WHERE lang = 'Java'
RETURN name, lang, projects
ORDER BY projects DESC;

// io.thecodeforge: WITH + COLLECT — build lists per group
MATCH (cat:Category)<-[:IN_CATEGORY]-(product:Product)
WITH cat, collect(product.name) AS products, count(product) AS product_count
WHERE product_count >= 5
RETURN cat.name AS category, products, product_count
ORDER BY product_count DESC;

// io.thecodeforge: COUNT — two forms matter
// count(*) counts all rows including nulls
// count(order) counts only non-null orders
MATCH (user:User)
OPTIONAL MATCH (user)-[:PLACED]->(order:Order)
RETURN user.name,
       count(*) AS total_rows,           // includes users with no orders
       count(order) AS orders_placed      // excludes null orders
ORDER BY orders_placed DESC;

// io.thecodeforge: COLLECT — build nested lists
// Group products by category, return category with its products
MATCH (cat:Category)<-[:IN_CATEGORY]-(product:Product)
WITH cat.name AS category, collect(product) AS products
RETURN category, size(products) AS count, products
ORDER BY count DESC;

// io.thecodeforge: COLLECT with property extraction — cleaner output
// Collect just the names, not full node objects
MATCH (proj:Project)<-[:CONTRIBUTES_TO]-(dev:Developer)
WITH proj.name AS project, collect(dev.name) AS contributors
RETURN project, contributors
ORDER BY size(contributors) DESC;

// io.thecodeforge: SUM and AVG — numeric aggregations
MATCH (order:Order)-[:CONTAINS]->(item:OrderItem)
WITH order,
     sum(item.quantity * item.unit_price) AS order_total
RETURN avg(order_total) AS avg_order_value,
       min(order_total) AS min_order,
       max(order_total) AS max_order,
       sum(order_total) AS total_revenue,
       count(order) AS total_orders;

// io.thecodeforge: PERCENTILE — distribution analysis
MATCH (user:User)-[:PLACED]->(order:Order)
WITH user, count(order) AS order_count
RETURN percentileCont(order_count, 0.5) AS median_orders,
       percentileCont(order_count, 0.95) AS p95_orders,
       percentileCont(order_count, 0.99) AS p99_orders,
       avg(order_count) AS mean_orders;

// io.thecodeforge: COLLECT for top-N per group
// Top 3 products per category by rating
MATCH (cat:Category)<-[:IN_CATEGORY]-(product:Product)
WITH cat, product
ORDER BY product.rating DESC
WITH cat.name AS category, collect(product)[0..3] AS top_products
RETURN category, top_products;

// io.thecodeforge: Friends of friends — 2 hops exactly
MATCH (me:Person {name: 'Alice'})-[:FRIEND*2]->(fof)
WHERE fof <> me  // exclude Alice herself
RETURN DISTINCT fof.name AS friend_of_friend
ORDER BY fof.name;

// io.thecodeforge: Friends up to 3 levels deep — 1 to 3 hops
MATCH (me:Person {name: 'Alice'})-[:FRIEND*1..3]->(connection)
WHERE connection <> me
RETURN DISTINCT connection.name AS connection,
       min(length(shortestPath((me)-[:FRIEND*]->(connection)))) AS degrees
ORDER BY degrees, connection.name;

// io.thecodeforge: Supply chain — find suppliers of suppliers
MATCH (product:Product {name: 'Widget Pro'})-[:SUPPLIED_BY*1..4]->(supplier)
RETURN product.name AS product,
       supplier.name AS ultimate_supplier,
       supplier.country AS origin_country;

// io.thecodeforge: Shortest path between two people
MATCH path = shortestPath(
  (alice:Person {name: 'Alice'})-[:FRIEND*]-(bob:Person {name: 'Bob'})
)
RETURN [n IN nodes(path) | n.name] AS path_names,
       length(path) AS degrees_of_separation;

// io.thecodeforge: All shortest paths (if multiple equally-short routes exist)
MATCH path = allShortestPaths(
  (alice:Person {name: 'Alice'})-[:FRIEND*]-(bob:Person {name: 'Bob'})
)
RETURN [n IN nodes(path) | n.name] AS path_names,
       length(path) AS hops;

// io.thecodeforge: Bounded traversal with filtering at each hop
// Find paths where every intermediate person is over 25
MATCH (alice:Person {name: 'Alice'})-[:FRIEND*1..3]->(target:Person)
WHERE all(node IN nodes(path) WHERE node.age >= 25 OR node = alice OR node = target)
RETURN DISTINCT target.name, length(path) AS hops;

// io.thecodeforge: UNWIND — expand a list into rows
UNWIND ['Alice', 'Bob', 'Charlie'] AS name
MERGE (p:Person {name: name})
RETURN p.name;

// io.thecodeforge: UNWIND for batch creation — 1 query, 100 nodes
// In production, $users comes from a JSON request body
UNWIND $users AS user_data
MERGE (u:User {email: user_data.email})
ON CREATE SET u.name = user_data.name,
              u.created_at = datetime()
RETURN count(u) AS users_processed;

// io.thecodeforge: UNWIND to create relationships in batch
// Connect a list of skills to a developer in one query
MATCH (dev:Developer {name: 'Alice'})
UNWIND ['Java', 'Spring Boot', 'Neo4j', 'Kubernetes'] AS skill_name
MERGE (s:Skill {name: skill_name})
MERGE (dev)-[:HAS_SKILL {level: 'expert'}]->(s)
RETURN dev.name, collect(s.name) AS skills;

// io.thecodeforge: UNWIND after COLLECT — flatten and reprocess
// Find all categories, collect their products, then flatten for analysis
MATCH (cat:Category)<-[:IN_CATEGORY]-(product:Product)
WITH cat, collect(product) AS products
UNWIND products AS product
RETURN cat.name AS category,
       product.name AS product,
       product.price AS price
ORDER BY cat.name, product.price DESC;

// io.thecodeforge: UNWIND with index — track position in the list
UNWIND ['alpha', 'beta', 'gamma', 'delta'] AS item
WITH item, range(0, 3)[indexOf(['alpha','beta','gamma','delta'], item)] AS idx
MERGE (n:Item {name: item})
SET n.sort_order = idx
RETURN n.name, n.sort_order
ORDER BY n.sort_order;

// io.thecodeforge: UNWIND for data cleanup — split comma-separated values
MATCH (legacy:LegacyRecord)
WHERE legacy.tags IS NOT NULL
WITH legacy, split(legacy.tags, ',') AS tag_list
UNWIND tag_list AS tag
MERGE (t:Tag {name: trim(tag)})
MERGE (legacy)-[:TAGGED_WITH]->(t)
RETURN count(DISTINCT legacy) AS records_processed, count(DISTINCT t) AS tags_created;

// io.thecodeforge: CALL {} — inline subquery for isolated aggregation
// Find developers, and for each, count their projects in a subquery
MATCH (dev:Developer)
CALL {
  WITH dev
  MATCH (dev)-[:CONTRIBUTES_TO]->(proj:Project)
  RETURN count(proj) AS project_count, collect(proj.name) AS project_names
}
RETURN dev.name, project_count, project_names
ORDER BY project_count DESC;

// io.thecodeforge: CALL {} — find the most recent order per user
MATCH (user:User)
CALL {
  WITH user
  MATCH (user)-[:PLACED]->(order:Order)
  RETURN order
  ORDER BY order.created_at DESC
  LIMIT 1
}
RETURN user.name, order.order_id, order.total, order.created_at
ORDER BY order.created_at DESC;

// io.thecodeforge: CALL db.indexes — list all indexes in the database
CALL db.indexes()
YIELD name, type, entityType, labelsOrTypes, properties, state
RETURN name, type, entityType, labelsOrTypes, properties, state
ORDER BY entityType, name;

// io.thecodeforge: CALL db.schema.visualization — see the graph schema
CALL db.schema.visualization()
YIELD nodes, relationships
RETURN nodes, relationships;

// io.thecodeforge: CALL apoc (if APOC is installed) — advanced procedures
// Export a subgraph to JSON for backup or migration
CALL apoc.export.json.query(
  'MATCH (u:User)-[:PLACED]->(o:Order) RETURN u, o LIMIT 100',
  null,
  {stream: true}
)
YIELD data
RETURN data;

// io.thecodeforge: CALL with UNION — combine results from multiple patterns
MATCH (dev:Developer {name: 'Alice'})
CALL {
  WITH dev
  MATCH (dev)-[:CONTRIBUTES_TO]->(proj:Project)
  RETURN proj.name AS item, 'project' AS type
  UNION
  WITH dev
  MATCH (dev)-[:HAS_SKILL]->(skill:Skill)
  RETURN skill.name AS item, 'skill' AS type
}
RETURN dev.name, collect({name: item, type: type}) AS contributions;

// io.thecodeforge: Create a range index — for fast property lookups
CREATE INDEX user_email_index FOR (u:User) ON (u.email);
CREATE INDEX product_sku_index FOR (p:Product) ON (p.sku);
CREATE INDEX order_status_index FOR (o:Order) ON (o.status);

// io.thecodeforge: Create a composite index — for queries that filter on multiple props
CREATE INDEX user_tenant_email_index FOR (u:User) ON (u.tenant_id, u.email);

// io.thecodeforge: Create a text index — for CONTAINS and ENDS WITH queries
CREATE TEXT INDEX product_name_text_index FOR (p:Product) ON (p.name);

// io.thecodeforge: Create a uniqueness constraint — prevents duplicate entities
CREATE CONSTRAINT user_email_unique FOR (u:User) REQUIRE u.email IS UNIQUE;
CREATE CONSTRAINT product_sku_unique FOR (p:Product) REQUIRE p.sku IS UNIQUE;
CREATE CONSTRAINT order_id_unique FOR (o:Order) REQUIRE o.order_id IS UNIQUE;

// io.thecodeforge: Create a node key — composite uniqueness + existence
CREATE CONSTRAINT user_tenant_key FOR (u:User) REQUIRE (u.email, u.tenant_id) IS NODE KEY;

// io.thecodeforge: Create an existence constraint — property must exist
CREATE CONSTRAINT user_email_exists FOR (u:User) REQUIRE u.email IS NOT NULL;

// io.thecodeforge: List all indexes and their status
SHOW INDEXES YIELD name, type, entityType, labelsOrTypes, properties, state
ORDER BY entityType, name;

// io.thecodeforge: List all constraints
SHOW CONSTRAINTS YIELD name, type, entityType, labelsOrTypes, properties
ORDER BY entityType, name;

// io.thecodeforge: Drop an index (if you need to recreate it)
DROP INDEX user_email_index;

// io.thecodeforge: Drop a constraint
DROP CONSTRAINT user_email_unique;

// io.thecodeforge: RECOMMENDATION ENGINE
// 'Customers who bought this also bought' — collaborative filtering
MATCH (customer:Customer)-[:PURCHASED]->(product:Product {sku: 'WIDGET-PRO'})
<-[:PURCHASED]-(other:Customer)-[:PURCHASED]->(recommended:Product)
WHERE recommended <> product  // exclude the product they already bought
  AND NOT (customer)-[:PURCHASED]->(recommended)  // exclude what they already own
RETURN recommended.name AS product,
       recommended.sku AS sku,
       count(other) AS bought_by_others
ORDER BY bought_by_others DESC
LIMIT 5;

// io.thecodeforge: FRAUD DETECTION — shared identity signals
// Find accounts that share a phone number OR address — potential fraud ring
MATCH (a1:Account)-[:HAS_PHONE]->(phone:Phone)<-[:HAS_PHONE]-(a2:Account)
WHERE a1 <> a2
WITH a1, a2, collect(DISTINCT phone.number) AS shared_phones
MATCH (a1)-[:HAS_ADDRESS]->(addr:Address)<-[:HAS_ADDRESS]-(a2)
WITH a1, a2, shared_phones, collect(DISTINCT addr.full_address) AS shared_addresses
WHERE size(shared_phones) >= 1 AND size(shared_addresses) >= 1
RETURN a1.account_id AS account_1,
       a2.account_id AS account_2,
       shared_phones,
       shared_addresses,
       size(shared_phones) + size(shared_addresses) AS risk_score
ORDER BY risk_score DESC
LIMIT 20;

// io.thecodeforge: SOCIAL GRAPH — mutual friends suggestion
MATCH (me:Person {name: 'Alice'})-[:FRIEND]->(mutual:Person)-[:FRIEND]->(suggestion:Person)
WHERE NOT (me)-[:FRIEND]->(suggestion)  // not already friends
  AND suggestion <> me                  // not yourself
RETURN suggestion.name AS suggested_friend,
       count(mutual) AS mutual_friends,
       collect(mutual.name) AS through
ORDER BY mutual_friends DESC
LIMIT 10;

// io.thecodeforge: SOCIAL GRAPH — community detection (simplified)
// Find groups of 3+ people who are all friends with each other
MATCH (a:Person)-[:FRIEND]->(b:Person)-[:FRIEND]->(c:Person)-[:FRIEND]->(a)
WHERE id(a) < id(b) AND id(b) < id(c)  // avoid counting same triangle multiple times
RETURN a.name, b.name, c.name AS triangle
ORDER BY a.name;

// io.thecodeforge: KNOWLEDGE GRAPH — find all entities connected to a concept
MATCH (concept:Concept {name: 'Microservices'})-[:RELATED_TO*1..2]->(related)
WHERE related <> concept
RETURN DISTINCT labels(related)[0] AS type,
       related.name AS entity,
       related.description AS description
ORDER BY type, entity
LIMIT 20;

// io.thecodeforge: EXPLAIN — show plan without executing (safe for production)
EXPLAIN
MATCH (dev:Developer {language: 'Java'})-[:CONTRIBUTED_TO]->(proj:Project)
WHERE proj.status = 'active'
RETURN dev.name, proj.name;

// io.thecodeforge: PROFILE — execute and show real metrics
PROFILE
MATCH (dev:Developer {language: 'Java'})-[:CONTRIBUTED_TO]->(proj:Project)
WHERE proj.status = 'active'
RETURN dev.name, proj.name;

// io.thecodeforge: BAD QUERY — no label, forces full scan
// This scans every node in the database
EXPLAIN
MATCH (n)
WHERE n.email = 'alice@thecodeforge.io'
RETURN n;

// io.thecodeforge: GOOD QUERY — uses label + index
// This uses the User label index and the email property index
EXPLAIN
MATCH (u:User {email: 'alice@thecodeforge.io'})
RETURN u;

// io.thecodeforge: Identifying the Eager operator
// If you see Eager in the plan, the engine is buffering all results
EXPLAIN
MATCH (order:Order)
WITH order
ORDER BY order.created_at DESC
MATCH (order)-[:CONTAINS]->(item:OrderItem)
RETURN order, collect(item) AS items;

// io.thecodeforge: Checking if an index is being used
// Run this after creating an index to verify the plan uses it
EXPLAIN
MATCH (u:User {email: 'test@thecodeforge.io'})
RETURN u.name;
// If the plan shows NodeIndexSeek, the index is working.
// If it shows NodeByLabelScan, the index isn't being used — check your query.

// io.thecodeforge — database tutorial

// Fool: runs this and wonders why 10M nodes crash
MATCH (n) RETURN n LIMIT 1000;

// Senior: knows to filter first
MATCH (n:User {status: 'active'})
RETURN n.name, n.created_at
LIMIT 1000;

// io.thecodeforge — database tutorial

// Verify your setup before writing anything
RETURN neo4j.version() AS version,
       apoc.version() AS apoc_version;

// Expected output (yours will differ by minor version)
// ╒═════════════════╤════════════════╕
//│version           │apoc_version    │
//╞═════════════════╪════════════════╡
//│'5.21.0'          │'5.21.0'        │
//└─────────────────┴────────────────┘

// io.thecodeforge — database tutorial

// BAD: string interpolation — don't do this
MATCH (u:User {name: 'Alice'})
RETURN u

// GOOD: parameterized query — do this
MATCH (u:User {name: $userName})
RETURN u

// Pass parameters as a map:
// { "userName": "Alice" }

// io.thecodeforge — database tutorial

// Install APOC (run once in Neo4J Browser or via config)
CALL dbms.listComponents() YIELD versions
WHERE name = 'APOC'
RETURN versions

// Example: use APOC for CSV import
LOAD CSV WITH HEADERS FROM 'file:///users.csv' AS row
CALL apoc.create.node(['User'], {id: row.id, name: row.name}) YIELD node
RETURN count(node)

// io.thecodeforge — database tutorial

CREATE CONSTRAINT team_id_unique IF NOT EXISTS
FOR (t:Team) REQUIRE t.id IS UNIQUE;

LOAD CSV WITH HEADERS FROM 'https://example.com/football/teams.csv' AS row
WITH row
WHERE row.id IS NOT NULL
MERGE (t:Team {id: toInteger(row.id)})
SET t.name = row.name, t.country = row.country
RETURN count(t) AS teams_created;

LOAD CSV WITH HEADERS FROM 'https://example.com/football/matches.csv' AS row
WITH row
WHERE row.home_team_id IS NOT NULL
MATCH (home:Team {id: toInteger(row.home_team_id)})
MATCH (away:Team {id: toInteger(row.away_team_id)})
CREATE (g:Game {date: date(row.date), home_score: toInteger(row.home_score), away_score: toInteger(row.away_score)})
CREATE (home)-[:PLAYED_AT_HOME]->(g)
CREATE (away)-[:PLAYED_AWAY]->(g);

// io.thecodeforge — database tutorial

// Find the shortest path between two people in a social graph
MATCH path = shortestPath(
  (alice:Person {name: 'Alice'})-[*..6]-(bob:Person {name: 'Bob'})
)
RETURN [node IN nodes(path) | node.name] AS chain,
       length(path) AS degrees_of_separation;

// Run PageRank on a collaboration network (requires GDS plugin)
CALL gds.pageRank.write({
  nodeProjection: 'Person',
  relationshipProjection: 'KNOWS',
  writeProperty: 'pagerank'
});