Agile

GitHub link for the website landing page: https://github.com/kelbie/agile-resources

Neo4j instructions

Get terms for each user

• Helpful docs: https://codex.wordpress.org/Database_Description

This query gets the skills (and other meta data) for each user and returns it into a single table. Currently some users are missing and I don't know why.

SELECT terms.name, terms.slug, users.user_login FROM xXTK6_terms AS terms
  LEFT JOIN xXTK6_termmeta AS termmeta
    ON terms.term_id = termmeta.term_id 
  INNER JOIN xXTK6_term_taxonomy AS term_taxonomy 
    ON terms.term_id = term_taxonomy.term_id 
  INNER JOIN xXTK6_term_relationships AS term_relationships
    ON term_relationships.term_taxonomy_id = term_taxonomy.term_taxonomy_id
  LEFT JOIN xXTK6_posts as posts
    ON term_relationships.object_id = posts.ID
  LEFT JOIN xXTK6_users as users
    ON posts.post_author = users.ID
  INNER JOIN xXTK6_usermeta as usermeta
    ON usermeta.user_id = users.ID
  WHERE usermeta.meta_key = 'xXTK6_capabilities' AND
        usermeta.meta_value LIKE '%candidate%' AND
        term_taxonomy.taxonomy = 'iwj_cat'
    ORDER BY users.user_login;

This is the sql relationships we are interested in:

usersmeta -> users <- posts <- term_relationships -> term_taxonomy -> terms

Import Data from MariaDB

Prerequisite:

• Download: https://mariadb.com/kb/en/about-mariadb-connector-j/
• Read these helpful docs: https://neo4j.com/developer/kb/how-do-i-use-cypher-to-connect-to-a-rbms-using-jdbc/

CALL apoc.load.jdbc('jdbc:mariadb://localhost:3306/<db>?user=<user>&password=<password>','
  SELECT terms.name, terms.slug, users.user_login FROM xXTK6_terms AS terms
    LEFT JOIN xXTK6_termmeta AS termmeta
      ON terms.term_id = termmeta.term_id 
    INNER JOIN xXTK6_term_taxonomy AS term_taxonomy 
      ON terms.term_id = term_taxonomy.term_id 
    INNER JOIN xXTK6_term_relationships AS term_relationships
      ON term_relationships.term_taxonomy_id = term_taxonomy.term_taxonomy_id
    LEFT JOIN xXTK6_posts as posts
      ON term_relationships.object_id = posts.ID
    LEFT JOIN xXTK6_users as users
      ON posts.post_author = users.ID
    INNER JOIN xXTK6_usermeta as usermeta
      ON usermeta.user_id = users.ID
    WHERE usermeta.meta_key = \'xXTK6_capabilities\' AND
          usermeta.meta_value LIKE \'%candidate%\' AND
          term_taxonomy.taxonomy = \'iwj_cat\'
      ORDER BY users.user_login;
') YIELD row  
MERGE (u:User {id: row.user_login})
MERGE (t:Term {id: row.slug})
  WITH u,t,row
MERGE (u)-[r:HAS_TERM]->(t)

Result

To get an exhaustive list of all the connected nodes use:

MATCH (n) MATCH (n)-[r]-() RETURN n,r

To get the nearest users based on similar terms, this is similar to Amazons "people who bought this also bought these x items":

MATCH (u)-[:HAS_TERM]->(t)-[:HAS_TERM]-(u2) WHERE u.id='<name>' 
RETURN u,t,u2

Suppose you search for a term that only applies to a few people, you could use this query to get other people that have similar terms ordered by frequency which should find most relevant people.

MATCH (t)<-[:HAS_TERM]-(u)-[:HAS_TERM]->(t2)-[:HAS_TERM]-(u2) WHERE t.id='<term>' 
RETURN u2.id as name, count(u2) as frequency
ORDER BY frequency DESC

Integrating it into Wordpress

In order to integrate it into Wordpress you will need to host Neo4j somewhere. Ash sent me the link to login to AWS but I wasn't able to get it working. Once its hosted somewhere you will have an IP and a port from which you can connect to the database, above I have shown the command to import the data into Neo4j.

On the PHP side I found an example I was working through that showed how to connect PHP to Neo4j. This probably makes more sense to John or Ash though because I'm not by any means an expert at PHP. You just have to be sure to install GraphAware, I used composer.

In the WordPress site you will have to swap in the Neo4j code instead of whatever SQL code its running by default.