By yngens on

I am trying to create a simple custom module, which outputs the list of nodes with comment counts exceeding a value defined by a node author in a CCK field called goal.

Example: Author creates 4 nodes and sets 10, 20, 5, 7 as the goals for them. Later on those nodes get the following number of comments respectively: 3, 25, 5, 6. So my custom module should generate a list consisting of only those nodes, which comment counts equal or exceed set goals. In a given example only second and third nodes satisfy the condition and get to the list - 3 is less than 10, 25 is more than 20, 5 equals 5 and 6 is less than 7.

So I guess the following MySql query should suffice:


SELECT node.title AS node_title, node.nid AS nid, node_comment_statistics.comment_count AS node_comment_statistics_comment_count, 'node' AS field_data_field_goal_node_entity_type
FROM 
{node} node
INNER JOIN {node_comment_statistics} node_comment_statistics ON node.nid = node_comment_statistics.nid
WHERE ((node.status = '1') AND (node_comment_statistics_comment_count => field_data_field_goal_node_entity_type))
LIMIT 100

Now, how can I turn this query to db_select query to use in my module? Instructions on http://api.drupal.org/api/drupal/includes!database!database.inc/function... help, but it is too difficult for a none-coder.

⋅ Categories: Drupal 7.x

Comments

jordojuice’s picture

jordojuice commented

You just want the db_select() equivalent of that query?

$query = db_select('node', 'n');
$query->fields('n', array('title', 'nid'));
$query->join('node_comment_statistics', 'c', 'n.nid = c.nid');
$query->addField('c', 'comment_count');
$query->join('field_data_field_goal_node_entity_type', 'goal', 'n.nid = goal.entity_id');
$query->condition('n.status', 1, '=');
$query->condition('c.comment_count', 'goal.field_goal_node_entity_type_value', '>=');
$query->execute()->fetchAll();

...didn't test it, but something like that. Of course, field_goal_node_entity_type_value should be the name of the value field for the goal field, I'm just not sure what that is.

yngens’s picture

yngens commented

Thanks Jordan!

I've modified my function in a custom module as:

function completed_petitions_contents(){
$query = db_select('node', 'n');
$query->fields('n', array('title', 'nid'));
$query->join('node_comment_statistics', 'c', 'n.nid = c.nid');
$query->addField('c', 'comment_count');
$query->join('field_data_field_goal_node_entity_type', 'goal', 'n.nid = goal.entity_id');
$query->condition('n.status', 1, '=');
$query->condition('c.comment_count', 'goal.field_goal_node_entity_type_value', '>=');
$query->execute()->fetchAll();
return $query;
}

and it gives the error:

PDOException: SQLSTATE[42S02]: Base table or view not found: 1146 Table 'myusername.field_data_field_goal_node_entity_type' doesn't exist.
#0 /opt/drupal-7.12/includes/database/database.inc(2139): PDOStatement->execute(Array)
#1 /opt/drupal-7.12/includes/database/database.inc(664): DatabaseStatementBase->execute(Array, Array)
#2 /opt/drupal-7.12/includes/database/select.inc(1264): DatabaseConnection->query('SELECT n.title ...', Array, Array)
#3 /home/myusername/public_html/sites/default/modules/completed/completed.module(48): SelectQuery->execute()
#4 /home/myusername/public_html/sites/default/modules/completed/completed.module(77): completed_contents()
#5 [internal function]: completed_block_view('completed')
#6 /opt/drupal-7.12/includes/module.inc(795): call_user_func_array('completed', Array)
#7 /opt/drupal-7.12/modules/block/block.module(853): module_invoke('completed', 'block_view', 'completed')
#8 /opt/drupal-7.12/modules/block/block.module(674): _block_render_blocks(Array)
#9 /opt/drupal-7.12/modules/block/block.module(318): block_list('sidebar_second')
#10 /opt/drupal-7.12/modules/block/block.module(268): block_get_blocks_by_region('sidebar_second')
#11 /opt/drupal-7.12/includes/common.inc(5578): block_page_build(Array)
#12 /opt/drupal-7.12/includes/common.inc(2582): drupal_render_page(Array)
#13 /opt/drupal-7.12/includes/common.inc(2470): drupal_deliver_html_page(Array)
#14 /opt/drupal-7.12/includes/menu.inc(532): drupal_deliver_page(Array, '')
#15 /home/myusername/public_html/index.php(21): menu_execute_active_handler()
#16 {main}
$_REQUEST:
Array
(
)


REQUEST_URI:
/admin/structure/block/manage/block/5/configure
jordojuice’s picture

jordojuice commented

Have you looked at your database in phpMyAdmin to see what the table names are? It looks like my_username is somehow being used as the name or alias of the goal field.
'myusername.field_data_field_goal_node_entity_type' should be 'the_table_alias.field_data_field_goal_node_entity_type' . In the case of my example, the alias is goal, which I defined in the second argument of the join() method. It's hard to debug this one remotely.

Check out http://drupal.org/node/310075 for some great info on the Drupal 7 database API. There are many examples there and maybe that can help you figure this out.

yngens’s picture

yngens commented

I also noticed and didn't like this peace of query in SELECT:

'node' AS field_data_field_goal_node_entity_type

however I copied this query from the one which was generated by views. So maybe query should be completely different.

The 'goal' is the name of a CCK field. So basically I am trying to compare the values of two entities per node: (1) comment count and (2) a cck field called 'goal'. I'll be looking at examples on http://drupal.org/node/310075, but it is really difficult for a non-coder to figure it out. Would appreciate if anyone could help me to solve this issue.

yngens’s picture

yngens commented

I simplified task and want to first successfully list nodes which have comments at all. So modifying your query to:

function completed_petitions_contents(){

$query = db_select('node', 'n');
$query->fields('n', array('title', 'nid'));
$query->join('node_comment_statistics', 'c', 'n.nid = c.nid');
$query->addField('c', 'comment_count');
$query->condition('n.status', 1, '=');
$query->condition('c.comment_count', 0, '>');
$query->execute()->fetchAll();

  return $query;

returns empty list, though there are lot's of commented nodes on my test site.

Second question is about execute line. On http://drupal.org/node/310075#fields I see only '$query->execute()' on other examples, so i there a principal difference between '$query->execute()' and '$query->execute()->fetchAll()' in your example?

yngens’s picture

yngens commented

Thanks to help of timplunkett in IRC I have modified my code to the following: 

function completed_petitions_contents(){

$query = db_select('node', 'n');
$query->fields('n', array('title', 'nid'));
$query->join('node_comment_statistics', 'c', 'n.nid = c.nid');
$query->addField('c', 'comment_count');
$query->join('field_data_field_goal', 'goal', 'n.nid = goal.entity_id');
$query->condition('n.status', 1, '=');
$query->condition('c.comment_count', 'goal.field_goal_value', '>=');

$results = $query->execute()->fetchAll(); return $results;
}

and it started to generate output, but unfortunately the list contains not only nodes, comment counts of which exceed the set goals.

jordojuice’s picture

jordojuice commented

Nice! Glad we could help.

yngens’s picture

yngens commented

Jordan, I appreciate your help, but unfortunately the list still generates nodes, which don't meet the condition. My last query is

function completed_petitions_contents(){
$query = db_select('node', 'n');
$query->fields('n', array('title', 'nid'));
$query->join('node_comment_statistics', 'c', 'n.nid = c.nid');
$query->join('field_data_field_goal', 'g', 'n.nid = g.entity_id');
$query->condition('n.status', 1, '=');
$query->condition('c.comment_count','g.field_goal_value','>');
$results = $query->execute()->fetchAll(); return $results;
}

and it works fine, but for some reason the line $query->condition('c.comment_count','g.field_goal_value','>'); doesn't work correctly.

And what is strange when I replace it with $query->condition('c.comment_count',0,'>'); it indeed generates the list of nodes with comments more than 0 and when I replace it with $query->condition('g.field_goal_value',0,'>'); it generates the list of nodes with only goals set above 0.

However when compared with each other they fail to generate right list.

casey_2000’s picture

casey_2000 commented

It's because of this

$query->condition('c.comment_count','g.field_goal_value','>');

In mysql U'll get
where c.comment_count > 'g.field_goal_value'
So you are checking if it is bigger then string.

Have anybody found the way to compare two different fields?

wouters_f’s picture

wouters_f commented

I believe you were looking for this:

$query->where('p.sku  <> b.field_orgbarcode_value');

Found on https://www.drupal.org/node/310086

Add me on linkedin:
http://be.linkedin.com/in/woutersfrederik