0

I am new to the Database API and SQL queries in general. I needed to create a custom module that could be given a list of fields and would output the average value of each of these fields. I am pleased to have got my module to do this.

Unfortunately it includes all of the values for my fields, irregardless of whether they belong to a published node or not. To get it to exclude results from unpublished nodes I'm guessing that my query needs to draw in information from the node table. Could someone please point me in the right direction of how to exclude results from unpublished nodes?

Please find my module code below.

    function custom_aggregate_data_block_info() {
  // This example comes from node.module.

   $blocks['custom_data'] = array(
    'info' => t('Custom Data'),
// DRUPAL_CACHE_PER_ROLE will be assumed.
  ); 

  return $blocks;

}


/**
* Implements hook_block_view().
*
* Prepares the contents of the block.
*/

function custom_aggregate_data_block_view($delta = '') {
  // This example is adapted from node.module.
  $block = array();

  switch ($delta) {

      case 'custom_data':


    // initial output for block
        $block['subject'] = t('feild test');        
        $blockoutput = "<div id='results'>";

    // searches the database and displays the results in a block

      /* database table name, table cell name */
      /* ie DRUPAL_MACH_NAME_value, field_data_DRUPAL_MACH_NAME */
      $feilds_to_query = array ("field_test_2004","field_test_2005");

      foreach ($feilds_to_query as $dbfeild) {
          // Do something with each $record
          $dbfeild_tablename = $dbfeild + "_value";
          $dbfeild_cellname = "field_data_" + $dbfeild;
          $querystring = "SELECT " + $dbfeild_tablename + " FROM " + $dbfeild_cellname;
          $query =  db_query($querystring);

          $total_field_value = 0;
          $total_records_field_value = 0;
          $average_records_field_value = 0;

          foreach ($query as $record) {
              // Do something with each $record
              $total_field_value = $total_field_value + $record->$dbfeild_tablename;
              $total_records_field_value = $total_records_field_value + 1;
          }

          // when looping through the results is finished then average the total by the number of records returned.
        $average_records_field_value = $total_field_value/$total_records_field_value ;  

        // add the averaged data to the values stored ready to be returned
        $blockoutput .= "<div>Average results: " . $dbfeild . ": " . $average_records_field_value . "</div>";

       }

       // when finished looping through all the queries then close the html div, then return the results as a block 
       $blockoutput .= "</div> <!--end #results -->";
       $block['content'] = $blockoutput ;


    break;
  }
  return $block;
}

Note that I have the case statement included as I plan to make use of several blocks in the completed module.

0

Whether a node is published is stored in the node table, in the 'status' column. You can do an innerJoin with that table and add a condition. I'd do it something like this, using db_select (haven't tested this...):

$fields_to_query = array ("field_test_2004","field_test_2005");

foreach ($fields_to_query as $dbfield) {
  // Do something with each $record
  $dbfield_tablename = $dbfield . "_value";
  $dbfield_cellname = "field_data_" . $dbfield;

  $query = db_select($dbfield_cellname, 'c');
  $query->addField('c', $db_field_tablename, 'value');
  $query->condition('n.status', 1);
  $query->innerJoin('node', 'n', 'n.nid=c.entity_id');
  $result = $query->execute();

  foreach($result as $r) {
  //fetch field value by calling $r->value
  ...
  }
  ...
}
| improve this answer | |
1

The node table has a field called status - 1 is published, 0 is unpublished.

Are field_test_2004 and field_test_2005 Drupal generated tables?

Normally table names take the form of field_data_field_test_2004...

Anyway, assuming they are Drupal generated tables they will have a field called entity_id which you can join to the node table with.

If you are building queries like this I would suggest you look into using Drupal Dynamic Queries as opposed to string concatenation - it is much easier to add bits to and manipulate the query.

Also feilds is spelt fields (sorry, couldn't help myself)

| improve this answer | |
0

olyN's answer is correct and helped me greatly. For anyone trying to do something similar here is my code:

case 'custom_data':

    // searches the database and outputs the results as a block

    // initial output for block
        $block['subject'] = t('field test');        
        $blockoutput = "<div id='results'>"; 

      /* database table name, table cell name */
      /* ie DRUPAL_MACH_NAME_value, field_data_DRUPAL_MACH_NAME  */
      $fields_to_query = array ("field_test_2004","field_test_2005");

      foreach ($fields_to_query as $dbfield) {
          // Do something with each $record
          $dbfield_cellname = $dbfield . "_value";
          $dbfield_tablename = "field_data_" . $dbfield;

            $query = db_select($dbfield_tablename, 'c');
            // Add extra detail to this query object: a condition, fields and a range

            $query->addField('c', $dbfield_cellname, 'value');
            $query->innerJoin('node', 'n', 'n.nid=c.entity_id');
            $query->condition('n.status', 1);

            $result = $query->execute();

              $number_of_rows = $result->rowCount();
              $myresults = $result->fetchAll();

               $total = 0;

                for ($x=0; $x<$number_of_rows; $x++) // loop for each of the number of results returned
                  {
                   $total = $total + $myresults[$x]->value;
                  }

                  // add to the output block for each table returned
                  $blockoutput .= "<div>RESULTS: " . $dbfield_tablename. " averaged total: " . $total . "</div>";

      } // end for foreach ($fields_to_query as $dbfield) 


       // when finished looping through all the queries then close the html div, then return the results as a block 
       $blockoutput .= "</div> <!--end #results -->";
       $block['content'] = $blockoutput ;


    break;
| improve this answer | |

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.