[Solved] Merge SELECT statements, possibly into a preprocessor

By computerbarry on 13 Nov 2015 at 12:46 UTC

I have a couple of SELECT statements which are nearly identical, which I'd like to combine, avoiding DRY, and if possible, use inside a preprocessor inside template.php.

As things stand, I use the SELECTS separately (shown below) by placing them onto my node-override.tpl.php

// returns the average of the nodes comments
$sql = "
  SELECT ROUND(AVG(r.field_comment_rating_value),1)
  FROM comment c
  JOIN field_data_field_comment_rating r
  ON c.cid = r.entity_id
  WHERE c.nid = :nid
  AND c.status = 1";

$result = db_query($sql, array(':nid' => $nid));
$avg_value = $result->fetchField();

// returns the count of nodes comments
$sql2 = "
    SELECT COUNT(c.cid)
    FROM comment c
    WHERE c.nid = :nid
    AND c.status = 1";

$result2 = db_query($sql2, array(':nid' => $nid));
$commentCount = $result2->fetchField();

Could we somehow combine both SELECTs into one, or select both the COUNT and AVG inside the same SELECT?

And could we, or how could this be used inside my preprocessor so I can use the variables on node-override.tpl.php:

function theme1_preprocess_venue(&$variables) {
}

My goal:
I'd like to print $commentCount and $avg_value on my node-override.tpl.php based on a db_query preprocessor coded inside template.php. ideally, using one query.

Thanks, Barry

Comments

The comment count is already

nevets commented 13 November 2015 at 14:57

The comment count is already available in node.tpl.php and its variations as $comment_count

For the average you can use

function theme1_preprocess_node(&$variables) {
  $node = $variables['node'];

// returns the average of the nodes comments
$sql = "
  SELECT ROUND(AVG(r.field_comment_rating_value),1)
  FROM comment c
  JOIN field_data_field_comment_rating r
  ON c.cid = r.entity_id
  WHERE c.nid = :nid
  AND c.status = 1";

$result = db_query($sql, array(':nid' => $node->nid));
$variables['avg_value'] = $result->fetchField();
}

and then the value will be available in the template as $avg_value

Impressive :)

computerbarry commented 14 November 2015 at 12:08

Impressive :)

I didn't realise, I was under the assumption to use a preprocess, we needed everything to be attached to the new variable. This makes things a little clearer, thanks.

Ok, from what I can gather, couple of questions:

The SELECT COUNT is no longer needed because we can now use $comment_count, which does the same thing in this instance, and is available to all nodes out of the box?
What do we achieve by adding $node = $variables['node'] at the top?

We have changed the below snippets, what was the overall purpose of each:

//$result = db_query($sql, array(':nid' => $nid));
$result = db_query($sql, array(':nid' => $node->nid));

//$avgRating = $result->fetchField();
$variables['avgRating'] = $result->fetchField();

Everything works very well nevets, just trying to get an understanding now of what we have done.

--------- Bonus question

I currently have an if else inside node-override.tpl.php

<?php if ($comment_count > 0 ): ?>
          <?php print $comment_count; ?> Reviews
        <?php else: ?>
          <strong class='review-hook'> 0 reviews 
            <?php echo l('Be the first','#comment-form'); ?>
          </strong>
        <?php endif; ?>

Should I keep this inside node-override.tpl.php, or is there a way of adding this into the code. Best practice? I read somewhere that no 'if else statements' should be added to your .tpl.php files if possible.

Cheers, Barry

The more you learn.... the more you learn there is more to learn.

Regarding 1) correct.

nevets commented 14 November 2015 at 15:32

Regarding 1) correct.

Regarding 2), the only variable available to hook_preprocess_node() is $variable and while you could user $variables['node']->nid, I prefer to the approach shown.

Regarding the bonus question, without using the custom node-override.tpl.php, that is pretty much what is show for comments, I would quess one of the variable already does that. As for if/else logic in templates, it is used all the time for conditionally output html so I would go with what you have.