I am migrating comments from a Drupal 6 site into Drupal 7 - but I am importing into the Reply module https://drupal.org/project/reply because the comments were on a Profile node in Drupal 6 but now I'm using Profile2 entities in D7.

I am trying to connect to my legacy database and so far my query is like

$query = $this->sourceDbConnection()
  ->select('node', 'n')
  ->fields('n', array('nid', 'title', 'uid',))
  ->fields('c', array('cid', 'pid', 'nid',  'uid', 'subject',
    'comment', 'hostname', 'timestamp', 'status', 'format',
    'thread', 'name', 'mail', 'homepage',
$query->innerJoin('comments', 'c', '(n.nid=c.nid)');
$query->where('(n.type=:type)', array(':type' => $this->LEGACY_CONTENT_TYPE));

However, I wish to add an ORDER BY to the query similar to how it is used in the comment_render function https://api.drupal.org/api/drupal/modules%21comment%21comment.module/function/comment_render/6 i.e. ORDER BY SUBSTRING(c.thread, 1, (LENGTH(c.thread) - 1)) and I don't know how to do this.


SelectQuery::orderBy() isn't particularly picky, you can just tack this on to the end of your code:

$query->orderBy('SUBSTRING(c.thread, 1, (LENGTH(c.thread) - 1))');

With the rest of your code that produces

SELECT n.nid AS nid, n.title AS title, n.uid AS uid, c.cid AS cid, c.pid AS pid, c.nid AS c_nid, c.uid AS c_uid, c.subject AS subject, c.comment AS comment, c.hostname AS hostname, c.timestamp AS timestamp, c.status AS status, c.format AS format, c.thread AS thread, c.name AS name, c.mail AS mail, c.homepage AS homepage
{node} n
INNER JOIN {comments} c ON (n.nid=c.nid)
WHERE  ((n.type=:type)) 
ORDER BY SUBSTRING(c.thread, 1, (LENGTH(c.thread) - 1)) ASC
| improve this answer | |
  • Great. Thank you. There was I thinking I had to use $query->addExpression – SkidNCrashwell Oct 24 '13 at 15:40

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.