RSS

Tag Archives: drupal

How to get the Drupal 7 posts from mysql

It’s snippet time!

I needed to quickly extract all the articles on a Drupal site.
Every article has its own taxonomy term on a custom dictionary (myDictionary) so I needed to extract which term every article is set up.
Here we go:

SELECT
    FROM_UNIXTIME(N.created) as created
    , NT.name as t_name
    , TTD.name as keyword
    , N.title
    , FM.uri AS image
    , B.body_value as body
  FROM
    node N
    INNER JOIN node_type NT ON N.type = NT.type
    INNER JOIN taxonomy_index TI ON TI.nid = N.nid
    INNER JOIN taxonomy_term_data TTD ON TI.tid = TTD.tid
    INNER JOIN taxonomy_vocabulary TV ON TV.vid = TTD.vid
    INNER JOIN field_data_body B ON B.entity_id = N.nid AND B.entity_type = 'node'
    INNER JOIN file_usage FU ON FU.id = N.nid /* this is to get the images */
    INNER JOIN file_managed FM ON FU.fid = FM.fid /* this is to get the images */
WHERE TV.name = 'myDictionary'  /* this is to filter the needed dictionary  */
ORDER BY N.created

Drupal 7 ER schema

 
1 Comment

Posted by on 2014/08/22 in dev

 

Tags: ,