Currently I’m working on migrating a Drupal 7 website to WordPress, in the process I’ve learned alot about the Drupal database schema. So this post is a place for me to store handy queries and notes. Its a Work in Progress so I’ll add more over the coming weeks.
Content types in Drupal are similar to post types in WordPress where fields can be added, however in the database how the fields are handled is very different from WordPress. In Drupal fields are added as separate tables.
SQL to get multiple images ( stored as a collection item field in Drupal)
The field is called field_images stored in the table field_data_field_images table. Because the field is collection there is only 1 value stored in the field_data_field_images table ( the collection id). To get the individual items of the collection (images in this case), we need to join to file usage table on that id then to file_managed table on the fid. Then we have the information about the images ( filenames etc ).
select n.nid, n.title, m.* from node n
left join field_data_field_images f on n.nid = f.entity_id
left join file_usage u on f.field_images_value = u.id
left join file_managed m on u.fid = m.fid
where n.nid = node_id
Get all fields on a content_type
eg for content type ‘bobs_tyre’
SELECT field_name FROM field_config_instance ci WHERE ci.bundle = ‘bobs_tyre’