How are node and field tables related in MySQL?

Question

I am working on a query that involves the field_data_body Drupal table, and I have been asked to return corresponding node ids for the relevant rows in field_data_body.

However, after reaching a copy of what I think is the accurate database diagram for Drupal databases here -

https://www.drupal.org/files/er_db_schema_drupal_7.png

I don't see how the node table relates to the field_data_body table, so I don't know what kinds of joins I could do to connect them.

I am a DBA working with the database so I am using only SQL. Although if there is a better way to return a report linking these two (in PHP, etc.) I'd be grateful if someone could let me know that, too.

Thanks for any help, jrdevdba

Answer 1

Fields belong to entities; entities are generic, and the same field can be shared amongst any number of different entity types. So you'll find potentially data from a number of different entity types in any given field table.

All field tables take the same basic form, with the entity_type, entity_id, language and deleted columns used as the differential (with delta added to make up the primary key, and allow multiple cardinality).

So a manual join for a node to the body table might look like this:

SELECT title
FROM node n
INNER JOIN field_data_body b ON b.entity_type = 'node' AND b.entity_id = n.nid AND b.language = 'und' AND b.deleted = 0

Regarding a "better way", the Views module is usually the best solution for such things. I've never used it but I also understand Forena Reports might be useful too.