显示具有需要累积的多个值的左连接

问题描述：

I have a table of 'URLs', and each of the 'URLs' may have multiple 'conversions' attached to them. I want to accumulate the numerical value of all the conversions ($), and count the amount of conversions related to each URL aswell.

At the moment I'm using a left outer join to join the url table to the conversion table, but this just repeats the URL record if there's multiple conversions attached. I want to know what I can do to solve this, be it on the database side or logic side (I'm using PHP/CodeIgniter.)

If it helps, here is the CodeIgniter Active Record Query:

    $this->db->select('url.id, url.url, url.clicks,url.affiliate_url,conversion.commission');
    $this->db->from('url');
    $this->db->join('conversion', 'url.id = conversion.url_id','left outer');
    $this->db->where('url.user_id = '.$this->user_id);
    $this->db->order_by('url.id','DESC');
    $data['urls'] = $this->db->get()->result();

Thanks in advance!

Tom

Solution:

$this->db->select('url.id, url.url, url.clicks,url.affiliate_url,count(conversion.id) AS conversions,sum(conversion.commission) AS earnings');
$this->db->from('url');
$this->db->join('conversion', 'url.id = conversion.url_id','left outer');
$this->db->where('url.user_id = '.$this->user_id);
$this->db->group_by('url.id'); 
$this->db->order_by('url.id','DESC');
$data['urls'] = $this->db->get()->result();
enter code here

我有一个“网址”表格，每个“网址”可能附加了多个“转化次数” 他们。我想累积所有转化的数值（$），并计算与每个网址相关的转化次数。 p>

目前我正在使用左外连接来将url表加入转换表，但如果附加了多个转换，则只会重复URL记录。我想知道我能做些什么来解决这个问题，无论是在数据库方面还是在逻辑方面（我正在使用PHP / CodeIgniter。） p>

如果有帮助，这里是CodeIgniter 活动记录查询： p>

  $ this-＆gt; db-＆gt; select（'url.id，url.url，url.clicks，url.affiliate_url，conversion.commission'  ）; 
 $ this-＆gt; db-＆gt; from（'url'）; 
 $ this-＆gt; db-＆gt; join（'conversion'，'url.id = conversion.url_id'，'left outer  '）; 
 $ this-＆gt; db-＆gt; where（'url.user_id ='。$ this-＆gt; user_id）; 
 $ this-＆gt; db-＆gt; order_by（'url.id'，  'DESC'）; 
 $ data ['urls'] = $ this-＆gt; db-＆gt; get（） - ＆gt; result（）; 
  code>  pre> 
 
 提前致谢！ p> 
 
 
 Tom  p> 
 
 
解决方案： p> 
 
 
  $ this-＆gt; db  - ＆gt;选择（'url.id，url.url，url.clicks，url.affiliate_url，count（conversion.id）AS转换，sum（conversion.commission）AS收入'）; 
 $ this-＆gt; db  - ＆gt; from（'url'）; 
 $ this-＆gt; db-＆gt; join（'conversion'，'url.id = conversion.url_id'，'left outer'）; 
 $ this-＆gt;  db-＆gt; where（'url.user_id ='。$ this-＆gt; user  _id）; 
 $的这 - ＆GT; DB-＆GT; GROUP_BY（ 'url.id'）;  
 $ this-＆gt; db-＆gt; order_by（'url.id'，'DESC'）; 
 $ data ['urls'] = $ this-＆gt; db-＆gt; get（） - ＆gt;结果 （）; 
输入代码
  code>  pre> 
  div>

答

You need to group by the URL:

SELECT url.id, url.url, sum(url.clicks), count(url.clicks,url.affiliate_url), sum(conversion.commission)
FROM url
LEFT JOIN conversion ON url.id = conversion.url_id
WHERE url.user_id = ?
GROUP BY url.id, url.url
ORDER BY url.id DESC

Sorry it isn't in codeigniter, but I'm sure your able to convert.

答

this should do the trick.

$this->db->select('url.id, url.url, url.clicks,url.affiliate_url,conversion.commission');
$this->db->select_sum('conversion.commission');
$this->db->from('url');
$this->db->join('conversion', 'url.id = conversion.url_id','left outer');
$this->db->where('url.user_id = '.$this->user_id);
$this->db->group_by('url.id'); 
$this->db->order_by('url.id','DESC');

You need to group your results by url's id and sum up the conversions. good luck ;)

p.s. i haven't worked with ci, but these are the methods for what you need (lines 2 & 6)

显示具有需要累积的多个值的左连接

相关推荐