未获得正确SUM金额用户从线程和回复表投票 [英] Not getting correct SUM amount for users votes from thread and reply table

问题描述

我使用codeigniter 3.1.0，我试图从不同的表中获得两列的总SUM

如果我的 user_id = 1 它应该返回 421 的总和，但返回 431 它是在我的回答表中添加其他用户的投票

问题使用活动记录如何确保SUM获得用户ID的正确金额。

模型函数

  public function thread_reputation（$ user_id）{
 $ this-> db-> select（'SUM（reply.votes + thread.votes）AS reputation'）; 
 $ this-> db-> from（'thread'，'left'）; 
 $ this-> db-> join（'reply'，'reply.user_id = thread.user_id'，'left'）; 
 $ this-> db-> where（'thread.user_id'，$ user_id）; 
 $ this-> db-> where（'reply.user_id'，$ user_id）; 
 $ query = $ this-> db-> get（）; 
 
 if（$ query-> num_rows（）> 0）{
 return $ query-> row（）; 
} else {
 return 0; 
} 
} 
  

控制器部分，我在其中回显

  $ thread_reputation = $ this-> thread_analytics_model-> thread_reputation（'1'）; 
 echo $ thread_reputation-> reputation; 
  

线索表

解决方案

使用此更改您的查询

  $ query = $ this-> db-> query（SELECT SUM（A.votes）as reputation from 
（
 SELECT reply.votes FROM reply where reply.user_id = $ user_id 
 
 UNION ALL 
 
 SELECT thread.votes FROM thread where thread.user_id = $ user_id 
）AS A）; 
  

测试并正常工作

： -

使用活动REOCRD 查询

  $ this-> db-> select（SUM（A.votes）as reputation）; 
 $ this-> db-> from（（
 SELECT reply.votes FROM reply where reply.user_id = $ user_id 
 
 UNION ALL 
 
 SELECT thread.votes FROM thread where thread.user_id = $ user_id）AS A）; 
 $ query = $ this-> db-> get（）; 
  

I am using codeigniter 3.1.0 and I am trying to get the total SUM of two columns from different tables

If my user_id = 1 it should return the total sum of 421 but returns 431 It is adding the votes from other users on my reply table

Question Using Active Record How can I make sure the SUM is getting the correct amount for the users id.

Model Function

public function thread_reputation($user_id) {
    $this->db->select('SUM(reply.votes + thread.votes) AS reputation');
    $this->db->from('thread', 'left');
    $this->db->join('reply', 'reply.user_id = thread.user_id', 'left');
    $this->db->where('thread.user_id', $user_id);
    $this->db->where('reply.user_id', $user_id);
    $query = $this->db->get();

    if ($query->num_rows() > 0) {
        return $query->row();
    } else {
        return 0;
    }
}

Controller section where i echo it

$thread_reputation = $this->thread_analytics_model->thread_reputation('1');
echo $thread_reputation->reputation;

Thread Table

Reply Table

解决方案

Change your query with this

$query = $this->db->query("SELECT SUM(A.votes) as reputation from
    (
      SELECT reply.votes FROM reply where reply.user_id = $user_id

      UNION ALL

      SELECT thread.votes FROM thread where thread.user_id = $user_id               
    )AS A");

Tested and properly working

Check Screenshot:-

Query using ACTIVE REOCRD

$this->db->select("SUM(A.votes) as reputation");
$this->db->from("(
SELECT reply.votes FROM reply where reply.user_id = $user_id

UNION ALL

SELECT thread.votes FROM thread where thread.user_id = $user_id) AS A");
$query = $this->db->get();

这篇关于未获得正确SUM金额用户从线程和回复表投票的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！