I am trying to understand Views SQL, and am using a query of some Commerce data to increase my understanding. Here is the start of a query:

SELECT commerce_order.order_id AS order_id, 
AS commerce_customer_profile_field_data_commerce_customer_billi, ...

The first part of this is straightforward enough, it is just picking up the order_id from the commerce_order table. The second item, though is puzzling: the first part, commerce_customer_profile is just a table, but then the second part field_data_commerce_custom_billing is another table, and the two are connected by an underscore.

In other words, the construct looks like this: SELECT table1_table2.field. I have searched at length for an explanation of this syntax, but cannot find anything

  • It's probably just that combining the entity type and name of field that's attached to it gives Views a table alias that's guaranteed to be unique for its purposes – Clive Aug 16 '16 at 17:29

how table aliases are created in joins is pretty simple - to have unique alias views takes names of both tables and combines them. you can check more simple example: SELECT node.created AS node_created, node.nid AS nid, users_node.mail AS users_node_mail FROM {node} node LEFT JOIN {users} users_node ON node.uid = users_node.uid

users table is joined to node table and unique name becomes "users_node". For files table it would files_node.

I think it is the only way to have unique aliases. Also it is easy to understand from which join data is coming

  • What you are suggesting would explain a lot. It looks, though, like you are suggesting that the LEFT JOIN is defining a new "table" named users_node, and that it is that new "table" to which the SELECT statement is referring? I have not seen anything in SQL syntax that suggests that you can "name" a JOIN, and use that name in other SQL statements. Or is this maybe something that is unique to Drupal? – Wyckham Seelig Aug 17 '16 at 2:05
  • whenever you use table name in mysql you can create alias for it. check this link: dev.mysql.com/doc/refman/5.7/en/join.html "A table reference can be aliased using tbl_name AS alias_name or tbl_name alias_name". also this link explains how aliases can be used both for tables and columns: mysqltutorial.org/mysql-alias. In both links you can see that it is MySql syntax which allows to "name" join. I think it is similar with other SQL systems – Mantas Karanauskas Aug 17 '16 at 6:33

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.