Hey everyone.
I have been using Yii for over five years now, but the site I had was simple enough to at most require a 1:n relation between tables. Now, when I wanted to implement private messaging into my site, I quickly realized that implementing actual conversations would require an n:m relation. This looks like that:
ONE User
HAS MANY Conversations
ONE Conversation
HAS MANY Members (= Users that see the messages)
HAS MANY Messages
ONE Message
HAS ONE Conversation
Okay, so this is a typic n:m relation and requires a link between the Users and Conversations, since one convo can have many members, while one user can have many convos.
So I built this lil’ SQL:
CREATE TABLE IF NOT EXISTS `tbl_user_pm_conv_members` (
`user_id` int(11),
`conv_id` int(11)
);
CREATE TABLE IF NOT EXISTS `tbl_user_pm_conv` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`owner_id` int(11) NOT NULL,
`subject` varchar(255) NOT NULL,
PRIMARY KEY (`id`)
);
CREATE TABLE IF NOT EXISTS `tbl_user_pm_msg` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`conv_id` int(11) NOT NULL,
`from_id` int(11) NOT NULL,
`body` text NOT NULL,
`sent` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (`id`)
);
(I saw a note about constraints, i dont have them added cause I dont understand why I even need them…)
So far, so good. "tbl_user_pm_conv_members" points towards a conversation and a user. It does not need a primary key, therefore.
And now the bummer. I tried EVERY possible variation that I could find, and this is how I started out.
User.php:
<?php class User extends CActiveRecord {
// ...
public function relations() {
return [
"convos"=>[
self::MANY_MANY,
"PrivateConversation",
"tbl_user_pm_conv_members(id,convo_id)"
]
];
}
// ...
} ?>
PrivateConversation.php:
<?php class PrivateConversation extends CActiveRecord {
// ...
public function relations() {
return [
"messages"=>array(self::HAS_MANY, "PrivateMessage", "conv_id"),
"owner"=>array(self::BELONGS_TO, "User", "id"),
'members'=>array(
self::MANY_MANY,
"User",
"tbl_user_pm_conv_members(id,conv_id)",
),
];
}
// ...
} ?>
PrivateConversationMembers.php:
<?php class PrivateConversationMembers extends CActiveRecord {
public function relations() {
return [
"member"=>array(self::BELONGS_TO, "User", "user_id"),
"convo"=>array(self::BELONGS_TO, "PrivateConversation", "conv_id")
];
}
public function tableName() {
return "{{user_pm_conv_members}}";
}
} ?>
PrivateMessage should be obvious.
So…how exactly do I tell my models to work? Because I always get an "unknown column in OR clause" error. Horray, very informative. So it would be awesome if someonje could help me to deal with this, thanks!
Kind regards, Ingwie!
