6

What should the dynamic query for the following SQL string be?

SELECT p.id, p.name, GROUP_CONCAT(s.name) AS site_list
FROM sites s
INNER JOIN publications p ON(s.id = p.site_id)
GROUP BY p.id;
8
$query = db_select('sites', 's')
  ->fields('p', array('id', 'name'))
  ->groupBy('p.id');

$query->addExpression('GROUP_CONCAT(s.name)', 'site_list');
$query->join('publications', 'p', 's.id = p.site_id');
2
  • 4
    Note that GROUP_CONCAT is a MySQL specific feature, make sure that there's no better way to solve this. – Berdir Aug 13 '12 at 8:34
  • 1
    Note MySQL has a size limit on text returned by GROUP_CONCAT. The default value is 1024,so if you are querying to get a large number of items, it can truncate the results on you. It doesn't warn you, of course, and I couldn't even find anything in the error logs about this. – Bhanu Prakash Ryaga Apr 17 '17 at 9:50
0

You can use that query string directly with db_query(), with the difference that database table names need to be wrapped in curly brackets.

$query = db_query("SELECT p.id, p.name, GROUP_CONCAT(s.name) AS site_list FROM {sites} s INNER JOIN {publications p} ON p.id = s.site_id GROUP BY p.id");
$records = $query->fetchAll();
foreach ($records as $record) {
  // Do something.
}

I take that ON s.id = p.site_id really meant ON p.id = s.site_id, as p.id is the selected table field.

As Berdir said, GROUP_CONCAT() isn't implemented by all the database engines supported by Drupal; the query should be rewritten to avoid using it.

As Introduction to dynamic queries says, dynamic queries should be used:

  • When the query parts vary depending on the context
  • When other modules should be able to alter the executed query
  • When porting Drupal 6 code that uses db_rewrite_sql()

The following code (taken from comment_get_display_ordinal()) is an example of the first point.

$query = db_select('comment', 'c1');
$query->innerJoin('comment', 'c2', 'c2.nid = c1.nid');
$query->addExpression('COUNT(*)', 'count');
$query->condition('c2.cid', $cid);
if (!user_access('administer comments')) {
  $query->condition('c1.status', COMMENT_PUBLISHED);
}
$mode = variable_get('comment_default_mode_' . $node_type, COMMENT_MODE_THREADED);
if ($mode == COMMENT_MODE_FLAT) {
  $query->condition('c1.cid', $cid, '<');
}
else {
  $query->where('SUBSTRING(c1.thread, 1, (LENGTH(c1.thread) -1)) < SUBSTRING(c2.thread, 1, (LENGTH(c2.thread) -1))');
}
return $query->execute()->fetchField();

As part of the query is added basing on some conditions, db_select() is used. The alternative would be using db_query() and concatenate the strings that form the query (which isn't preferable to using db_select()).

An example of the second point is code that needs to show a list of nodes to which the currently logged-in user has access. In this case, db_select() is used, and node_access is added to the query with SelectQuery::addTag(), which allows modules that implement hook_query_TAG_alter() to alter the query, as explained in Query alteration (tagging).

The following code is an example of the third point. (See blog_feed_user().)

  $nids = db_select('node', 'n')->fields('n', array('nid', 'created'))
    ->condition('type', 'blog')
    ->condition('uid', $account->uid)
    ->condition('status', 1)
    ->orderBy('created', 'DESC')
    ->range(0, variable_get('feed_default_items', 10))
    ->addTag('node_access')
    ->execute()
    ->fetchCol();
  $channel['title'] = t("!name's blog", array(
    '!name' => format_username($account),
  ));
  $channel['link'] = url('blog/' . $account->uid, array(
    'absolute' => TRUE,
  ));
  node_feed($nids, $channel);

That code is the Drupal 7 port of the following code.

  $result = db_query_range(db_rewrite_sql("SELECT n.nid, n.created FROM {node} n  WHERE n.type = 'blog' AND n.uid = %d AND n.status = 1 ORDER BY n.created DESC"), $account->uid, 0, variable_get('feed_default_items', 10));
  $channel['title'] = t("!name's blog", array(
    '!name' => $account->name,
  ));
  $channel['link'] = url('blog/' . $account->uid, array(
    'absolute' => TRUE,
  ));
  $items = array();
  while ($row = db_fetch_object($result)) {
    $items[] = $row->nid;
  }
  node_feed($items, $channel);

If your query falls in one of those cases, and you are showing just part of the query, then you can use db_select(), for example as the following code.

$query = db_select('publications', 'p')->fields('p', array('id', 'name'));
$alias = $query->innerJoin('sites', 's', '%alias.site_id = p.id');
$query->addExpression("GROUP_CONCAT($alias.name)", 'site_list');
$query->groupBy('p.id');

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.