php - 如何更改 MySQL 查询的结果排序？

Question

我正在尝试获取我的页面的上一个和下一个节点链接/缩略图，并根据它们的标题或文件 URI 或文件名对结果进行排序...

代码是查询数据库，根据节点ID(nid，n.nid)输出前后节点链接。我想根据节点标题 (title, n.title)、文件名 (filename, f.filename) 甚至文件 URI (uri, f.uri) 对结果进行排序。

但是，当我更改此行时:

->orderBy('n.nid', $order)

到:

->orderBy('n.title', $order)

那是行不通的。唯一的区别是，如果您从一个图片库的最后一页移动到另一个图片库，它会稍微改变顺序，但在其他方面，画廊内部的一切都是一样的。问题是如果您有一个画廊并决定在一段时间后插入一张新图像。节点 ID 现在与其他节点完全不同，此代码无法提取它。

我也尝试过更改显示 nid 的其他部分，但它不起作用。我想这对于更了解 MySQL 和 PHP 的人来说是微不足道的，但我已经坚持了几个小时，非常感谢任何帮助。

这是完整的代码(来自 Vlad Stratulat，最初发现 here ):

模板.php

function dad_prev_next($nid = NULL, $op = 'p', $start = 0) {
if ($op == 'p') {
    $sql_op = '>';
    $order = 'ASC';
}
elseif ($op == 'n') {
    $sql_op = '<';
    $order = 'DESC';
}
else {
    return NULL;
}

$output = '';

// your node must have an image type field
// let's say it's name is IMAGEFIELD
// select from node table
$query = db_select('node', 'n');
// join node table with image field table
$query->leftJoin('field_data_field_IMAGEFIELD', 'i', 'i.entity_id = n.nid');
// join file managed table where all data about managed files stored
$query->leftJoin('file_managed', 'f', 'f.fid = i.field_IMAGEFIELD_fid');
$query
    // select nid and title from node
    ->fields('n', array('nid', 'title'))
    // select uri from file_managed (image path)
    ->fields('f', array('uri'))
    // select image alt and title
    ->fields('i', array('field_IMAGEFIELD_alt', 'field_IMAGEFIELD_title'))
    // where nid "greater than"/"lower than" our current node nid
    ->condition('n.nid', $nid, $sql_op)
    // where node type in array('your content types')
    ->condition('n.type', array('PHOTOS'), 'IN')
    // where node is published
    ->condition('n.status', 1)
    // where requested node has image to display (if you want thumbnail)
    ->condition('f.uri', '', '!=')
    // order by nid
    ->orderBy('n.nid', $order)
    // limit result to 1
    ->range($start, 1);     

// make query
$result = $query->execute()->fetchAll();

foreach ($result as $node) {
    // theme your thumbnail image
    $variables = array(
        // default image style name `thumbnail`
        // you can use your own by following
        // admin/config/media/image-styles on your site
        'style_name' => 'thumbnail',
        'path' => $node->uri,
        'alt' => $node->field_IMAGEFIELD_alt,
        'title' => $node->field_IMAGEFIELD_title
    );
    $image = theme('image_style', $variables);

    $options = array(
        'html' => TRUE,
        'attributes' => array(
            'title' => $node->title
        )
    );
    $output = l($image, "node/{$node->nid}", $options);
}

return $output;
}

节点.tpl.php

<?php print dad_prev_next($node->nid, 'p', 0); ?>
<?php print dad_prev_next($node->nid, 'n', 0); ?>

编辑 2:

尝试使用 strcmp 函数:

function dad_prev_next($title = NULL, $op = 'p', $start = 0) {
if ($op == 'p') {
    $strcmp = '1';
    $order = 'ASC';
}
elseif ($op == 'n') {
    $strcmp = '2';
    $order = 'DESC';
}
else {
    return NULL;
}

$output = '';

// your node must have an image type field
// let's say it's name is IMAGEFIELD
// select from node table
$query = db_select('node', 'n');
// join node table with image field table
$query->leftJoin('field_data_field_IMAGEFIELD', 'i', 'i.entity_id = n.nid');
// join file managed table where all data about managed files stored
$query->leftJoin('file_managed', 'f', 'f.fid = i.field_IMAGEFIELD_fid');
$query
    // select nid and title from node
    ->fields('n', array('nid', 'title'))
    // select uri from file_managed (image path)
    ->fields('f', array('uri'))
    // select image alt and title
    ->fields('i', array('field_IMAGEFIELD_alt', 'field_IMAGEFIELD_title'))
    // where node type in array('your content types')
    ->condition('n.type', array('PHOTOS'), 'IN')
    // where node is published
    ->condition('n.status', 1)
    // where requested node has image to display (if you want thumbnail)
    ->condition('f.uri', '', '!=')
    // order by nid
    ->orderBy('n.title', $order)
    // limit result to 1
    ->range($start, 1);     

// make query
$result = $query->execute()->fetchAll();

foreach ($result as $node) {
    // theme your thumbnail image
    $variables = array(
        // default image style name `thumbnail`
        // you can use your own by following
        // admin/config/media/image-styles on your site
        'style_name' => 'thumbnail',
        'path' => $node->uri,
        'alt' => $node->field_IMAGEFIELD_alt,
        'title' => $node->field_IMAGEFIELD_title
    );
    $image = theme('image_style', $variables);

    $options = array(
        'html' => TRUE,
        'attributes' => array(
            'title' => $node->title
        )
    );
    $output = l($image, "node/{$node->nid}", $options);
}

return $output;
}

现在没有错误记录，但总是显示相同的照片 - 按字母顺序排列的列表中前两张和最后两张。

Answer 1

您正在按 n.title 排序，但您的第一个条件是寻找大于/小于 $nid 的 n.nid。这是没有意义的，并且会导致半随机选择。我建议在按标题排序时将条件更改为 n.title $node->title，这样就可以了。