主页 / user-1009466

Toniq's questions

Martin Hope
Toniq
Asked: 2025-04-09 19:44:00 +0800 CST
  • 2

此查询有效并返回预期结果：

$arr = [7,1];//dynamic
$vids = explode(',',$arr);
$in = implode(',', array_fill(0, count($ids), '%d'));

$stmt = $wpdb->prepare("
            SELECT mt.id, mt.title, mt.options, COUNT(DISTINCT ct.id) as comment_count, COUNT(DISTINCT lt.id) as like_count
            FROM $media_table as mt
            LEFT JOIN $comments_table ct ON ct.media_id = mt.id
            LEFT JOIN $like_table lt ON lt.media_id = mt.id
            WHERE mt.id IN ($in)
            GROUP BY mt.id 
            ORDER BY $sortOrder $sortDirection", $vids);

如果我print_r($stmt)得到：

     "SELECT mt.id, mt.title, mt.options, COUNT(DISTINCT ct.id) as comment_count, COUNT(DISTINCT lt.id) as like_count FROM wp_mvp_reel_media as mt LEFT JOIN wp_mvp_reel_comments ct ON ct.media_id = mt.id LEFT JOIN wp_mvp_reel_like lt ON lt.media_id = mt.id WHERE mt.id IN (7,1) GROUP BY mt.id ORDER BY order_id ASC"

当我添加MAX(lt.user_id = %d) AS user_liked)- 时查询失败。

如果我print_r($stmt)，则返回一个空字符串""：

    $stmt = $wpdb->prepare("
                SELECT mt.id, mt.title, mt.options, COUNT(DISTINCT ct.id) as comment_count, COUNT(DISTINCT lt.id) as like_count, MAX(lt.user_id = %d) AS user_liked
                FROM $media_table as mt
                LEFT JOIN $comments_table ct ON ct.media_id = mt.id
                LEFT JOIN $like_table lt ON lt.media_id = mt.id
                WHERE mt.id IN ($in)
                GROUP BY mt.id 
                ORDER BY $sortOrder $sortDirection", $user_id, $vids);
wordpress
Martin Hope
Toniq
Asked: 2025-04-04 04:21:01 +0800 CST
  • 4

我有两张桌子：

comments_table

`id` int(11) unsigned NOT NULL AUTO_INCREMENT,
`media_id` int(11) unsigned DEFAULT NULL,
`user_id` int(11) unsigned DEFAULT NULL,//user who commented
`title` varchar(300) DEFAULT NULL,
`url` varchar(300) DEFAULT NULL,
`c_date` datetime,
`comment` longtext DEFAULT NULL,
PRIMARY KEY (`id`),
INDEX `media_id` (`media_id`)

id         comment    user_id
1         foo             1
2         bla             2  
3         something       7 

votes_table

`comment_id` int(11) unsigned DEFAULT NULL,
`user_id` int(11) unsigned DEFAULT NULL,//user who voted
`vote` tinyint(1) DEFAULT 0,
INDEX `comment_id` (`comment_id`)

comment_id user_id vote
1         2       1
2         4       -1  
3         7       0
3         1       1
2         1       1

我有 2 个疑问（获取所有评论）：

$comments = $wpdb->get_results($wpdb->prepare("SELECT ct.id, ct.comment, ct.user_id, ct.user_display_name, ct.avatar, ct.c_date, SUM(DISTINCT vt.vote) AS vote
                    FROM $comments_table as ct 
                    LEFT JOIN $votes_table vt on ct.id = vt.comment_id 
                    WHERE ct.media_id=%d 
                    GROUP BY ct.id
                    ORDER BY ct.c_date DESC", $media_id), ARRAY_A);

获取用户投票的评论：

$user_votes = $wpdb->get_results($wpdb->prepare("SELECT vote, comment_id
                    FROM $votes_table
                    WHERE user_id=%d", $user_id), ARRAY_A);

这两个都按预期工作。是否可以在一个查询中完成这些操作？（出于性能原因）

例如，将结果 user_vote = votes_table.vote 添加到 comment_table.id votes_table.user_id 投票。

mysql
Martin Hope
Toniq
Asked: 2024-04-14 03:42:10 +0800 CST
  • 5

我想在鼠标滚轮滚动时触发scrollIntoView。当我单击按钮时，这是有效的，但在滚动时似乎不起作用（即使我将其限制为只是发生一个，如下例所示）

document.getElementById('next').addEventListener('click', function() {

  var target = document.getElementById('target')
  target.scrollIntoView({
    behavior: "smooth",
    block: 'nearest'
  });
})

var scrollActive

window.addEventListener("wheel", event => {



  const delta = Math.sign(event.deltaY);


  if (!scrollActive) {

    scrollActive = true;

    if (delta == 1) {

      console.info(delta);

      var target = document.getElementById('target')
      target.scrollIntoView({
        behavior: "smooth",
        block: 'nearest'
      });

    }

  }


});
.a {
  width: 50px;
  height: 250px;
  border: 1px solid #444;
  margin: 20px;
}
<a id="next" href="#">play next</a>

<div class="a">1</div>
<div class="a">2</div>
<div class="a">3</div>
<div class="a">4</div>
<div class="a" id="target">5</div>
<div class="a">6</div>
<div class="a">7</div>

javascript