[SOLVED] Drupal relation module (mysql)

[hlussa]

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]

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]

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.

[admin]

Awesome work!

[elijah]

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

[hlussa]

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