I’ve got 3 tables to store user-user_relation-log relation as below:
user(
id
agent_id
username ....
)
user_agent(
parent_id
user_id
distance
)
fund_log(
user_id, // whom is this log belongs
type,
amount,
)
user.agent_id is to save direct parent user’s id. the table user_agent save all parent-chld relation. For examle, user A recommend B then B recommend C, so that’s a A-B-C relation, and there are 2 records in user_agent like {parent_id:B.id, user_id:C.id, distance:1} and {parent_id:A.id, user_id:C.id, distance:2}.
Then I define a relation as below:
class User extends CActiveRecord{
public function relations(){
return array(
'fund_report' => array(self::STAT, 'FundLog', '{{user_agent}}(agent_id, user_id)',
'select' => 'SUM(fund_report.amount)',
'condition' => '`type` = 1',
),
);
}
}
then this will generate a sql as below
SELECT SUM(fund_report.amount) AS `s`, //fund_report is set to be wrong so that the sql can be display
`lt_user_agent`.`agent_id` AS `c0`
FROM `lt_fund_log` `t`
INNER JOIN `lt_user_agent` ON (`t`.`id`=`lt_user_agent`.`user_id`) // [b]here!, t.id is not the right key. it should be t.user_id[/b]
WHERE (TYPE = 1)
GROUP BY `lt_user_agent`.`agent_id` HAVING (`lt_user_agent`.`agent_id`=36)
how can I change the relation to make the t.id be t.user_id (INNER JOIN section) ?