By timnorman on

I could use a little help understanding a Views mysql query.

I understand mysql queries a little bit, but how this Views query is accessing data is baffling me. The only bit of information that comes close to answering my questions is from this page:

Understanding "node_data" & node references in Views SQL queries
http://drupal.org/node/336999

But I still don't get how it works.

Here is what I have... I've created a new content type of Photo a Day with an image and a description. Views is outputting a list of published images.

Here is the query Views creates: 

SELECT node.nid AS nid,
   node_data_field_image_photo_a_day.field_image_photo_a_day_fid AS node_data_field_image_photo_a_day_field_image_photo_a_day_fid,
   node_data_field_image_photo_a_day.field_image_photo_a_day_list AS node_data_field_image_photo_a_day_field_image_photo_a_day_list,
   node_data_field_image_photo_a_day.field_image_photo_a_day_data AS node_data_field_image_photo_a_day_field_image_photo_a_day_data,
   node_data_field_image_photo_a_day.nid AS node_data_field_image_photo_a_day_nid,
   node.type AS node_type,
   DATE_FORMAT((FROM_UNIXTIME(node.created) + INTERVAL -14400 SECOND), '%Y%m%d%H') AS node_created_hour
 FROM node node 
 LEFT JOIN content_type_photo_a_day node_data_field_image_photo_a_day ON node.vid = node_data_field_image_photo_a_day.vid
 WHERE (node.status <> 0) AND (node.type in ('photo_a_day'))
   ORDER BY node_created_hour ASC

The part I don't understand is the "node_data" reference. I don't see a table name starting with this. What table is node_data_field_image_photo_a_day referencing or aliasing? Is it the content_type_photo_a_day table or is it another table or field_name somewhere else in the database? I don't see a table labeled field_image_photo_a_day, but the field_image_photo_a_day_fid is found inside content_type_photo_a_day and there is a field_image_photo_a_day inside content_node_field table. I'm stumped on how or what it is going on in this query with node_data.

Also, assuming that node_data_field_image_photo_a_day is referencing a table why not just reference the table directly instead of aliasing it?

Why are there two "node" after the FROMi ? I know they tell the query what table to pull info from, but why twice?

Can anyone help me understand a bit more? Thank you!

⋅ Categories: Drupal 6.x

Comments

jimthunderbird’s picture

jimthunderbird commented

All you see are just table alias, they are perfectly ok.

If you want more information, you can see http://stackoverflow.com/questions/198196/when-to-use-sql-table-alias

Attitude is mind's paintbrush, it can color anything.

timnorman’s picture

timnorman commented

Thank you for the link. Helps explain it some more.

nevets’s picture

nevets commented

Second part first, FROM node node says use the table node and alias it as node.

For the first part look at the join LEFT JOIN content_type_photo_a_day node_data_field_image_photo_a_day which aliases content_type_photo_a_day as node_data_field_image_photo_a_day which explains its use through out the query.

My understanding is the aliases exist because a query might reference the same table twice and each would have a different alias.

timnorman’s picture

timnorman commented

Ahhh. I did not catch the LEFT JOIN aliasing. I thought aliasing had to be done after FROM and so I was totalling missing that in the query. Now this is making more sense.

I still don't get why Views would do an alias of node to a name of node. That seems redudant but perhaps if I was doing a more involved view the outputted query would need it "because a query might reference the same table twice and each would have a different alias."