Finding data using MySQL queries in the Node tables

Question

Outside of the Drupal system I am trying to get user data based off of the user table and export this information into a CSV.

I'm not quite understanding how the Node table system works and where to get my data let alone how to Join it in MySQL manually.

I have my users table, the standard users table from Drupal. I'm trying to get data from quite a few tables for the user, a simple one line profile type data per user. Under a regular database structure setup it would be as simple as a few joins to the right xref ids and tables and you're good to go. It's not quite working here of course.

One of the tables in question is called field_data_field_sexo_meta. This holds the information if the user is Male or Female.

As you can see the field field_sexo_meta_value holds the information. But I do now know how to join this table to the user ID.

I've tried quite a few tables to get the user ID to link to this entity_id field but nothing as of yet has worked. For instance I've tried field_data_field_paciente which has these fields:

The last field there is the uid field, but this doesn't link anything either.

I've also tried linking to the column uid in the nodes table and a type = 'termometro' but also it doesn't link to anything and I get null on my joins.

So evidently I'm missing which table links the field_data_field tables to the users.

This is my current query:

SELECT
  u.uid,
  u.name,
  u.mail,
  ur.rid,
  n.field_su_nutricionista_es_uid,
  s.field_sexo_meta_value
FROM users AS u
  LEFT JOIN users_roles ur
    ON (ur.uid = u.uid)
  LEFT JOIN field_data_field_usted_es p
    ON (p.field_usted_es_uid = u.uid)
  LEFT JOIN field_data_field_su_nutricionista_es n
    ON (n.entity_id = p.entity_id)
  LEFT JOIN field_data_field_sexo_meta s
    ON (s.entity_id = p.entity_id)
WHERE ur.rid = 5
     OR ur.rid = 4
GROUP BY uid

Answer 1

The concept that you are missing is that field data can be on any Drupal entity, nodes, users, etc. This means that when you JOIN, you need to add in some additional clauses. In particular, you need to check the entity type in the field data, as the entity ID isn't unique across entity types. You also need to check that the field value isn't deleted. So, you would have something like

LEFT JOIN field_data_field_usted_es p
  ON p.entity_id = u.uid AND p.entity_type = 'user' AND p.deleted = 0

Those three columns are typically all on need to JOIN ON. The rest of the columns would be what you return in the query, or use in a WHERE.

Most of the time, it is easiest to build up a query with Views, preview the SQL, and then use that as your starting point for a query outside of Drupal.

In your case, you could also use Views Data Export to generate your CSV inside Drupal itself.