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