Page 1 of 1
[SOLVED] Drupal relation module (mysql)
Posted: 02 Jun 2012 23:06
by 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
![Smile :-)](./images/smilies/icon_e_smile.gif)
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.
Re: Drupal relation module (mysql)
Posted: 05 Jun 2012 10:40
by 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.
Re: Drupal relation module (mysql)
Posted: 05 Jun 2012 16:18
by 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.
Re: [SOLVED] Drupal relation module (mysql)
Posted: 05 Jun 2012 17:07
by admin
Awesome work!
Re: [SOLVED] Drupal relation module (mysql)
Posted: 06 Jun 2012 18:52
by elijah
I'd be interested in doing some Drupal/Gephi integration, if you want some collaboration.
Re: [SOLVED] Drupal relation module (mysql)
Posted: 06 Jun 2012 21:27
by hlussa
I´m not much of a coder
![Smile :-)](./images/smilies/icon_e_smile.gif)
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