andser
July 12, 2010, 11:54am
1
Hi!
I have 2 tables :
CREATE TABLE IF NOT EXISTS `payedaccounts` (
`id` int(11) NOT NULL auto_increment,
`user_id` int(11) NOT NULL default '0',
`product_id` int(11) NOT NULL default '0',
`timestamp` int(11) default NULL,
`premium_expire` int(11) default NULL,
`transaction_amount` decimal(6,2) NOT NULL default '0.00',
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;
CREATE TABLE IF NOT EXISTS `sms_payments` (
`id` int(11) NOT NULL auto_increment,
`sms_id` varchar(20) default NULL,
`sms_number` varchar(10) default NULL,
`sms_operator` varchar(10) default NULL,
`sms_operator_full` varchar(50) default NULL,
`sms_phone` varchar(30) default NULL,
`sms_country` varchar(10) default NULL,
`sms_message` varchar(20) default NULL,
`sms_price` decimal(15,6) NOT NULL default '0.000000',
`abonent_price` decimal(15,6) NOT NULL default '0.000000',
`abonent_price_currency` varchar(10) default NULL,
`sms_date` int(11) default NULL,
`user_id` int(11) NOT NULL default '0',
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;
How to declare in PayedAccounts model relation between 2 columns :
LEFT OUTER JOIN `sms_payments` sms ON payedaccounts.user_id = sms.user_id
I have some code I think it’s wrong :
public function relations()
{
return array(
'sms_payments'=>array(self::HAS_MANY, 'SMSPayments', 'user_id'),
);
}
Thanks.
zaccaria
July 12, 2010, 1:39pm
2
Hi andser!
I guess that your relation should be written like that:
public function relations()
{
return array(
'sms_payments'=>array(self::HAS_MANY, 'SMSPayments', 'sms_id'),
);
}
Anyway the best way is to use INNO_DB as storage engine (if possible) and to set the foreign key explicitly. Doing that Gii will authomatically generate the relation correctly (plus the integrity reference check in mysql).
andser
July 12, 2010, 1:59pm
3
zaccaria:
public function relations()
{
return array(
'sms_payments'=>array(self::HAS_MANY, 'SMSPayments', 'sms_id'),
);
}
No. Columns must be related through user_id column in both tables.
zaccaria
July 12, 2010, 2:17pm
4
So I didn’t understood what kind of query you have to do, sorry.
You can set in user the relation:
public function relations()
{
return array(
'sms_payments'=>array(self::HAS_MANY, 'SMSPayments', 'user_id'),
);
}
And the same for the table sms. Having this you can do $user->sms_payments, or even $payedaccounts->user->sms_payments.
I don’t understand the meaninig of doing this kind of join, because is not a “standard” relationship.
You can achive it easily by setting ‘join’ property of CDbCriteria, but is a bit strange.
If, for example, a use has 3 record in payedaccounts and 4 record in sms_payments you will get 12 record.
Is this that you need?
megahertz
July 13, 2010, 9:31am
5
public function relations()
{
return array(
'sms_payments'=>array(self::HAS_MANY, 'SMSPayments', 'user_id', 'join' => 'LEFT OUTER JOIN sms_payments ON t.user_id = sms_payments.user_id'),
);
}
Sorry, but I don’t know whether it’s work
