gpt4 book ai didi

php - sql - 子查询计算分数

转载 作者:行者123 更新时间:2023-11-29 18:44:25 24 4
gpt4 key购买 nike

我有一个代码可以使用不同的表计算用户的分数我通过 Codeigniter 中的 php 和 sql 编写了这个,但这有一个大问题,速度太慢

 public function getTopUsers($request) {
// return $request;

$query = $this->db->query("
SELECT * FROM users
WHERE user_is_block = 0
AND user_is_paid = 1
ORDER BY id ASC"
)->result_array();

foreach($query as $key=>$value) {
unset($query[$key]['user_token']);
unset($query[$key]['user_confirmation_token']);
unset($query[$key]['user_password']);
$query[$key]['user_score'] = ( ($this->countInvitationByUserID($query[$key]['id']) * 1000) + ($this->getUserLikedCount($query[$key]['id'])) );
}

usort($query, function($a, $b) {
if($b['user_score'] == $a['user_score'])
return $a['id'] - $b['id'];
else
return $b['user_score'] - $a['user_score'];
});


return $query;
}


public function countcouponByUserID($id) {
$query = $this->db->query("
SELECT * FROM payment, coupon, users
WHERE payment.payment_coupon_id = coupon.id
AND coupon.coupon_token = users.user_coupon_token
AND users.id = ?
AND payment.payment_status = 1", array($id)
);
return $query->num_rows();
}

public function getUsertabCount($id) {
$query = $this->db->query("
SELECT * FROM `users`, post, `tab`
WHERE users.id = post.post_user_id
AND tab.tab_post_id = post.post_id
AND users.id = ?
AND post.post_is_active = 1", array($id)
);
return $query->num_rows();
}

现在的问题是这段代码太重而且慢我如何在一个查询中编写此代码

谢谢

最佳答案

您可以使用JOIN来计算单个查询中的所有内容,例如:

SELECT u.id, (COUNT(p.*) * 100  - COUNT(l.*)) AS score
FROM users u LEFT JOIN coupon c ON u.user_coupon_token = c.coupon_token
LEFT JOIN payment p ON p.payment_coupon_id = c.id
LEFT JOIN post po ON u.id = po.post_user_id
LEFT JOIN like l ON l.like_post_id = po.post_id
WHERE u.user_is_block = 0
AND u.user_is_paid = 1
AND (p.payment_status IS NULL OR p.payment_status = 1)
AND (po.post_is_active IS NULL OR po.post_is_active = 1)
GROUP BY u.id;

关于php - sql - 子查询计算分数,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/44735985/

24 4 0
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com