function _taxonomy_edge_generate_term_pretty_path_query in Taxonomy Edge 7

Generate query for ordering by dynamically compiled pretty path.

Parameters

integer $tid: Term ID to generate dynamic path from.

string $sep: Separator to use in path.

Return value

string String containing query.

File

./taxonomy_edge.module, line 709

Optimization of taxonomy data model for SQL performance.

Code

function _taxonomy_edge_generate_term_pretty_path_query($tid, $sep = ' > ') {
  $db_type = Database::getConnection()
    ->databaseType();
  switch ($db_type) {
    case 'pgsql':
      return "(SELECT array_to_string(array_agg((tpx.name)), '{$sep}') FROM (SELECT tpd.* FROM {taxonomy_term_edge} tpe INNER JOIN {taxonomy_term_data} tpd ON tpd.tid = tpe.parent WHERE tpe.tid = {$tid} ORDER BY tpe.distance DESC) tpx)";
    case 'mysql':
      return "(SELECT GROUP_CONCAT(tpd.name, ' (', tpd.tid, ')' ORDER BY tpe.distance DESC SEPARATOR '{$sep}') FROM {taxonomy_term_edge} tpe INNER JOIN {taxonomy_term_data} tpd ON tpd.tid = tpe.parent WHERE tpe.tid = {$tid})";
    case 'sqlite':
      return "(SELECT GROUP_CONCAT(val, '{$sep}') FROM (SELECT tpd.name AS val FROM {taxonomy_term_edge} tpe INNER JOIN {taxonomy_term_data} tpd ON tpd.tid = tpe.parent WHERE tpe.tid = {$tid} ORDER BY tpe.distance DESC))";
    case 'sqlsrv':
      return "(SELECT RTRIM((\n         SELECT tpd.name + '{$sep}'\n         FROM {taxonomy_term_edge} tpe\n         JOIN {taxonomy_term_data} tpd ON tpd.tid = tpe.parent\n         WHERE tpe.tid = {$tid}\n         ORDER BY tpe.distance DESC\n           FOR XML PATH (''))))";
    default:
  }
}