1

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

2

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.