turn a views query to a mysql query

Question

using the views raw sql module, i got for one view i have: but how this should be transformed, so as to be able to get the result, when i run the query into mysql directly? what do the {} stand for? i do not understand how the query should be altered so as to be able to have the results in mysql. My final goal is to replace all left joins into inner joins, so as to gain in speed, as described here: https://www.silviogutierrez.com/blog/optimizing-drupal-views-right-way/ so i have two questions, how should i alter below sql query to run in mysql, and how i could make all left joins below inner joins:

SELECT  node.nid AS nid, node.language AS node_language, node_field_data_field_title.title AS node_field_data_field_title_title,
        node.nid AS node_nid, 'node' AS field_data_field_circ_type_node_entity_type,
        'node' AS field_data_field_circ_period_node_entity_type,
        'node' AS field_data_field_active_node_entity_type, 'node' AS field_data_field_onhold_node_entity_type,
        'node' AS field_data_field_item_node_entity_type, 'node' AS field_data_field_patron_node_entity_type,
        'node' AS field_data_field_due_date_node_entity_typeFROM {node} nodeLEFT
    JOIN  {field_data_field_item} field_data_field_item  ON node.nid = field_data_field_item.entity_id
      AND  (field_data_field_item.entity_type = 'node'
              AND  field_data_field_item.deleted = '0'
           )LEFT
    JOIN  {node} node_field_data_field_item  ON field_data_field_item.field_item_nid = node_field_data_field_item.nidLEFT
    JOIN  {field_data_field_patron} field_data_field_patron  ON node.nid = field_data_field_patron.entity_id
      AND  (field_data_field_patron.entity_type = 'node'
              AND  field_data_field_patron.deleted = '0'
           )LEFT
    JOIN  {node} node_field_data_field_patron  ON field_data_field_patron.field_patron_nid = node_field_data_field_patron.nidLEFT
    JOIN  {og_membership} og_membership_node  ON node.nid = og_membership_node.etid
      AND  (og_membership_node.entity_type = 'node')LEFT
    JOIN  {field_data_field_title} node_field_data_field_item__field_data_field_title  ON node_field_data_field_item.nid = node_field_data_field_item__field_data_field_title.entity_id
      AND  (node_field_data_field_item__field_data_field_title.entity_type = 'node'
              AND  node_field_data_field_item__field_data_field_title.deleted = '0'
           )LEFT
    JOIN  {node} node_field_data_field_title  ON node_field_data_field_item__field_data_field_title.field_title_nid = node_field_data_field_title.nidWHERE (( (og_membership_node.gid = '672621' ) )AND(( (node.status = '1')
                              AND  (node.type IN ('circulation')) ))
                          )ORDER BY node_nid DESC LIMIT 20 OFFSET 0

Please improve the layout of your post, it's not readable at all. I also think your problem is not MySQL related but it's related to the PHP MYSQL functions. Also improve the title. It should be visible that you speak about the views-module of drupal! — André Pletschette
I fixed one typo when formatting -- DESCLIMIT needed a space. There may be others? — Rick James

André Pletschette André Pletschette · Accepted Answer · 2020-05-04T14:36:01

Everything you need is described in the Drupal documentation: https://www.drupal.org/docs/7/api/database-api/static-queries

Example from this site:

$result = db_query("SELECT nid, title FROM {node} WHERE created > :created", array(
  ':created' => REQUEST_TIME - 3600,
));

{node} in this example transforms into the real table name by adding suffixes for example.The same rules applies to other elements.

turn a views query to a mysql query

1 Answers