sql - 子查询计算分数 [英] sql - sub-queries calculate score

问题描述

我有一个代码可以为使用不同表的用户计算分数我是通过 php 在 Codeigniter 和 sql 中写的，但这有一个大问题，就是太慢了

I have a code that calculate an score for users using different table I wrote this via php in Codeigniter and sql but this have a big problem that is too slow

 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();
    }

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

Now the problem is that this code is too heavy and slow how I can wrote this code in just one query

谢谢

推荐答案

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

You can use JOIN to calculate everything in a single query, e .g.:

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;

这篇关于sql - 子查询计算分数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！