Is it possible to join dynamic tables (subqueries) using db_select()?

Question

I've got a complex query I'm trying to convert to db_select().

SELECT c.cid, c.pid, c.thread, count.replies, SUBSTRING(c.thread, 1, (LENGTH(c.thread) - 1)) as torder 
FROM comment c 
JOIN(
    SELECT count(*) as replies, SUBSTRING_INDEX(LEFT(thread, LENGTH(thread) -1 ), '.', 1) as torder 
    FROM comment 
    WHERE nid = 1 
    GROUP BY torder
) as count 
ON SUBSTRING_INDEX(LEFT(c.thread, LENGTH(c.thread) -1 ), '.', 1) = count.torder 
WHERE c.nid = 1 ORDER BY count.replies DESC, torder ASC;

I'm not sure how join through a select statement, if it is possible, or if there is another alternative.

I don't need to return anything from the join (count.replies is only there for show) so maybe there is another option other than join.

I'm trying to select order by the top-level comment with the highest number of replies and it's children.

The result prints this for my test data:

+-----+-----+-----------------+---------+----------------+
| cid | pid | thread          | replies | torder         |
+-----+-----+-----------------+---------+----------------+
| 262 |   0 | 03/             |      11 | 03             |
| 269 | 262 | 03.00/          |      11 | 03.00          |
| 272 | 269 | 03.00.00/       |      11 | 03.00.00       |
| 270 | 262 | 03.01/          |      11 | 03.01          |
| 273 | 270 | 03.01.00/       |      11 | 03.01.00       |
| 275 | 273 | 03.01.00.00/    |      11 | 03.01.00.00    |
| 276 | 275 | 03.01.00.00.00/ |      11 | 03.01.00.00.00 |
| 280 | 275 | 03.01.00.00.01/ |      11 | 03.01.00.00.01 |
| 277 | 270 | 03.01.01/       |      11 | 03.01.01       |
| 278 | 270 | 03.01.02/       |      11 | 03.01.02       |
| 271 | 262 | 03.02/          |      11 | 03.02          |
| 264 |   0 | 04/             |       3 | 04             |
| 281 | 264 | 04.00/          |       3 | 04.00          |
| 282 | 264 | 04.01/          |       3 | 04.01          |
| 260 |   0 | 01/             |       2 | 01             |
| 279 | 260 | 01.00/          |       2 | 01.00          |
| 261 |   0 | 02/             |       2 | 02             |
| 274 | 261 | 02.00/          |       2 | 02.00          |
| 283 |   0 | 05/             |       1 | 05             |
+-----+-----+-----------------+---------+----------------+

The 'replies' number includes the top level comment which is OK because I'm only using this for ordering and includes all of the comments in a thread for each comment which is by design because I want to group all of the comments first by how many comments are in their group.

The SELECT in the join returns the number of replies for each top level comment:

+---------+----------+
| replies | torder   |
+---------+----------+
|       2 | 01       |
|       2 | 02       |
|      11 | 03       |
|       3 | 04       |
|       1 | 05       |
+---------+----------+

I can't to this using db_query because I'm trying to overwrite the query in comment_get_thread() using hook_query_TAG_alter().

I've tried using $query->join() but don't see where it is possible to put in an argument.

EDIT:

Thanks so much for your answers. Here is the db_select translation of the sql query above if anyone is having trouble with this in the future:

$subquery = db_select('comment', 'c')
  ->fields('c', array('thread'))
  ->condition('c.nid', '1134');
$subquery->addExpression('COUNT(*)', 'replies');
$subquery->addExpression('SUBSTRING_INDEX(LEFT(c.thread, LENGTH(c.thread) -1 ), \'.\', 1)', 'toporder');
$subquery->groupBy('toporder');
$query = db_select('comment', 'c')
  ->fields('c', array('cid', 'pid'))
  ->condition('c.nid', '1134');
$query->addExpression('SUBSTRING(c.thread, 1, (LENGTH(c.thread) - 1))', 'torder');
$query->addJoin('', $subquery, 'count', 'SUBSTRING_INDEX(LEFT(c.thread, LENGTH(c.thread) -1 ), \'.\', 1) = count.toporder ');
$query->orderBy('count.replies', 'DESC');
$query->orderBy('torder', 'ASC');
$results = $query->execute()->fetchAll();

Answer 1

Both db_select and addJoin documentation says clearly that the table

May be a string or another SelectQuery object. If a query object is passed, it will be used as a subselect.

So,

$sub_select = db_select('comment');
// Convert.
$select = db_select('comment')->addJoin($sub_select...

Answer 2

I think you're looking for addJoin() ... I could be wrong ... here's a small example of adding a join to a db_select() from some random code I have ...

$query2 = db_select('dew_response', 'dr')->extend('TableSort')->orderByHeader($header);
$query2->addJoin('LEFT', 'dew_response_item', 'dri', 'dr.drid = dri.response_id');
$query2
  ->fields('dri', array('driid', 'item_id', 'response_id', 'answer'))
  ->fields('dr', array('drid'))
  ->condition('dri.survey_id', $survey_id, '=')
  ->condition('dri.response_id', $response_id, '=');
$result = $query2->execute();

Answer 3

Apply select query on the subquery result set.

  $sub_query = db_select('quiz_node_results', 'qnr');
  $sub_query->join('quiz_node_results_answers', 'qnra' , 'qnra.result_id = 
  qnr.result_id');
  $sub_query->condition('qnra.is_correct', 0, '=');
  $sub_query->fields('qnr', ['nid', 'score']);
  $sub_query->fields('qnra', ['question_nid', 'result_id', 'is_correct']);

  $main = db_select($sub_query, 't1');
  $main->join('node', 'n', 'n.nid = t1.question_nid');
  $main->fields('n', ['title']);
  $main->fields('t1', ['question_nid']);
  $main->addExpression('COUNT(t1.is_correct)', 'ctr');
  $main->groupBy('t1.question_nid');
  $main->orderBy('ctr', 'DESC');