[SOLVED] Drupal relation module (mysql)

Get help with your data
Post Reply
hlussa
Posts: 4
Joined: 02 Jun 2012 22:41
Location: Iceland

[SOLVED] Drupal relation module (mysql)

Post by hlussa » 02 Jun 2012 23:06

Big shoutout to the Gephi team for this mindblowing application!

Hopefully someone can help me out with this one:
I´m importing node relations from Drupal that I created using the amazing Relation module. Gephi and Drupal are my new friends now :-) I successfully imported all the nodes but I´m having difficulties importing the edges.The Relation module stores the source and the target them in the same column.

Here is the db schema used by the Relation module:

relation table

Code: Select all


rid - relation_type
 6  - works_for
field_data_endpoints table

Code: Select all


entity_id - endpoints_entity_id - endpoints_r_index
      6         14                          0
      6          7                           1

In this schema the source is "14" (index=0) and the target "7" (index=1). If the relationships are multi-dimensional I would get another row with an incremented index.

So.... I need help with the SQL query to pull this into Gephi. Please let me know if you can help.

Thanks in advance,
Hlussa.

hlussa
Posts: 4
Joined: 02 Jun 2012 22:41
Location: Iceland

Re: Drupal relation module (mysql)

Post by hlussa » 05 Jun 2012 10:40

After messing around with different sub-queries and trying all kinds of acrobatics with no understanding of or skills I finally found a solution: A self join.

Code: Select all

SELECT a.endpoints_entity_id AS source, b.endpoints_entity_id AS target, a.bundle AS label, a.entity_id  
FROM field_data_endpoints a, field_data_endpoints b  
WHERE a.entity_id=b.entity_id AND a.endpoints_entity_id<>b.endpoints_entity_id AND a.endpoints_r_index<>1 ORDER BY entity_id
This seems to be a hack but I´m getting the right results :-)

I have another question; Does Gephi support reverse labels on edges? Instead of a label from source to target (a donated to b) I could import a reverse label (b received bribes from a) :-)

Ok, thanks.

hlussa
Posts: 4
Joined: 02 Jun 2012 22:41
Location: Iceland

Re: Drupal relation module (mysql)

Post by hlussa » 05 Jun 2012 16:18

I messed around a little bit more and replaced the SELF JOIN method earlier with an INNER JOIN. I was unable to pull the label from the relation_type table before using the self join.
SELECT
a.endpoints_entity_id AS source,
b.endpoints_entity_id AS target,
a.entity_id,
c.label AS label
FROM
field_data_endpoints a

INNER JOIN
field_data_endpoints b
ON
a.entity_id=b.entity_id
INNER JOIN
relation_type c
ON
a.bundle=c.relation_type

WHERE

a.endpoints_entity_id<>b.endpoints_entity_id
AND
a.endpoints_r_index<>1
ORDER BY
entity_id;
Amazing stuff.

By using this method I have the opportunity to use Drupal as a platform to insert, update and delete nodes and relationships. The web interface is very comfortable and not to forget the connection between Gephi and the Drupal community.

Next task is to export from Gephi to Drupal, allowing data manipulation on both platforms.
Last edited by hlussa on 05 Jun 2012 19:53, edited 1 time in total.

admin
Gephi Community Manager
Posts: 964
Joined: 09 Dec 2009 14:41

Re: [SOLVED] Drupal relation module (mysql)

Post by admin » 05 Jun 2012 17:07

Awesome work!

elijah
Gephi Community Support
Posts: 169
Joined: 11 Sep 2010 18:09
Location: Stanford, CA
Contact:

Re: [SOLVED] Drupal relation module (mysql)

Post by elijah » 06 Jun 2012 18:52

I'd be interested in doing some Drupal/Gephi integration, if you want some collaboration.

hlussa
Posts: 4
Joined: 02 Jun 2012 22:41
Location: Iceland

Re: [SOLVED] Drupal relation module (mysql)

Post by hlussa » 06 Jun 2012 21:27

I´m not much of a coder :-) but I´m very interested in connecting these two platforms and can help with some basic stuff. Drupal (via the Relation module) is an extremely good web interface for Gephi which is by far the best graph application I´ve seen. There are some graph modules for Drupal that use the JIT library but I had problems integrating it with Relation. A lack of knowhow on my behalf I think.

Let me know what you have in mind.

Nonni

Post Reply