documents

docs.sql

SELECT 
  n.nid AS id, 
  f.filename AS filename,
  CASE t.gr_resource_type_tid WHEN 895 THEN 'document' WHEN 987 THEN 'presentation' ELSE 'resource' END as type, 
  REPLACE(REPLACE(ifnull(n.title,''),'\n',' '),'\r',' ') AS title,
  REPLACE(REPLACE(ifnull(bo.body_value,''),'\n',' '),'\r',' ') AS body,
  REPLACE(REPLACE(ifnull(ab.gr_abstract_value,''),'\n',' '),'\r',' ') AS abstract,
  REPLACE(REPLACE(ifnull(a.gr_author_value,''),'\n',' '),'\r',' ') AS author,   
  substring(da.gr_date_of_publication_value,0,10) AS publicationDate,
  u.gr_url_value AS url,
  FROM_UNIXTIME(n.created, '%Y-%m-%dT%T.914Z') AS created, 
  FROM_UNIXTIME(n.changed, '%Y-%m-%dT%T.914Z') AS modified 
FROM
  node n
    JOIN field_data_gr_resource_type t ON n.nid=t.entity_id AND t.gr_resource_type_tid IN (895,987)
    LEFT JOIN field_data_body bo ON n.nid=bo.entity_id 
    LEFT JOIN field_data_gr_abstract ab ON n.nid=ab.entity_id 
    LEFT JOIN field_data_gr_author a ON n.nid=a.entity_id 
    LEFT JOIN field_data_gr_rights r ON n.nid=r.entity_id 
    LEFT JOIN field_data_gr_rights_holder rh ON n.nid=rh.entity_id 
    LEFT JOIN field_data_gr_citation c ON n.nid=c.entity_id 
    LEFT JOIN field_data_gr_date_of_publication da ON n.nid=da.entity_id 
    LEFT JOIN field_data_gr_url u ON n.nid=u.entity_id 
    LEFT JOIN field_data_gr_image im ON n.nid=im.entity_id 
    LEFT JOIN file_managed fm ON im.gr_image_fid=fm.fid 
    LEFT JOIN field_data_gr_file fd ON n.nid=fd.entity_id
    LEFT JOIN file_managed f ON fd.gr_file_fid = f.fid    
WHERE
  n.status=1
INTO OUTFILE '/tmp/docs.txt'
FIELDS TERMINATED BY '|'