$15
Correct my Schema
The rules are the following:
An organization can have multiple campaigns.
A campaign can have multiple subscribers.
A rule may or may not be associated to a campaign.
A rule can have many commands -> A command can have many events.
A template or a campaign may or may or may not be associated to an event.
SPECIFIC PROBLEM: The relation between Rule->Commands->Events->Templates is not working. I had to patch this, by creating my own getCommands and getEvents methods and quering the db directly but of course this only works so far... i want this to work correctly.
Organization:
columns:
id:
primary: true
unique: true
type: integer
notnull: true
autoincrement: true
sf_guard_user_id:
type: integer(4)
name:
unique: true
type: string(255)
notnull: true
first_name:
type: string(255)
last_name:
type: string(255)
phone:
type: string(255)
email:
type: string(255)
logo:
type: string(255)
country:
type: string(255)
is_active:
default: true
type: boolean
notnull: true
indexes:
IX_Organization_1:
fields: [is_active]
IX_Organization_2:
fields: [id]
relations:
sfGuardUser:
foreign: id
local: sf_guard_user_id
owningSide: true
type: one
foreignType: one
Campaign:
columns:
id:
primary: true
unique: true
type: integer
notnull: true
autoincrement: true
organization_id:
type: integer
notnull: true
name:
unique: false
type: string(255)
notnull: true
description:
type: blob(0)
is_active:
default: true
type: boolean
notnull: true
indexes:
IX_Campaign_1:
fields: [id]
IX_Campaign_2:
fields: [is_active]
relations:
Organization:
local: organization_id
foreign: id
cascade: [delete]
Campaign:
foreign: campaign_id
local: id
type: many
Subscriber:
columns:
id:
primary: true
unique: true
type: integer
notnull: true
autoincrement: true
first_name:
type: string(255)
last_name:
type: string(255)
mobile:
type: string(20)
phone:
type: string(255)
email:
type: string(255)
country:
type: string(255)
is_active:
default: true
type: boolean
notnull: true
campaign_id:
type: integer
relations:
Campaign:
local: campaign_id
foreign: id
Message:
columns:
id:
primary: true
unique: true
type: integer
notnull: true
autoincrement: true
parent_id:
type: integer
notnull: false
sms_message_id:
type: string(30)
notnull: false
campaign_id:
type: integer
direction:
type: enum
values: [Inbound,Outbound]
keyword:
type: string(20)
msg_from:
type: string(20)
msg_to:
type: string(20)
msg_contents:
type: string(255)
msg_option:
type: string(255)
server_address:
type: string(255)
status:
type: enum
values: [Pending,Delivered,Processed,Failed]
response_type:
type: enum
values: [Normal,Unknown]
result_code:
type: string(255)
msg_option:
type: string(255)
data:
type: string(255)
delivery_type:
type: enum
values: [SMS,Email]
carrier:
type: string(100)
network_type:
type: enum
values: [gsm,cdma,tdma,iden]
received_at:
type: timestamp
relations:
Campaign:
local: campaign_id
foreign: id
Message:
local: parent_id
foreign: id
# Rules !
Session:
actAs: { Timestampable: ~ }
columns:
id:
primary: true
unique: true
type: integer
notnull: true
autoincrement: true
campaign_id:
type: integer
notnull: false
msg_from:
type: string(30)
notnull: true
subscriber_id:
type: integer
notnull: false
msg_contents:
type: string(255)
notnull: false
expires_at:
type: timestamp
is_active:
default: true
type: boolean
notnull: true
relations:
Campaign:
local: campaign_id
foreign: id
cascade: [delete]
Subscriber:
local: subscriber_id
foreign: id
type: many
cascade: [delete]
Rule:
columns:
id:
primary: true
unique: true
type: integer
notnull: true
autoincrement: true
type:
type: enum
values: [User,System]
campaign_id:
type: integer
notnull: false
msg_to:
type: string(20)
notnull: false
msg_from:
type: enum
values: [Anyone,Outbound]
description:
type: string(255)
notnull: false
is_active:
default: true
type: boolean
notnull: true
relations:
Campaign:
local: campaign_id
foreign: id
type: many
refClass: CampaignRules
class: Campaign
foreignAlias: Rules
cascade: [delete]
Command:
columns:
id:
primary: true
unique: true
type: integer
notnull: true
autoincrement: true
rule_id:
type: integer
notnull: true
command:
type: string(160)
notnull: false
relations:
Rule:
local: rule_id
foreign: id
cascade: [delete]
Event:
columns:
id:
primary: true
unique: true
type: integer
notnull: true
autoincrement: true
command_id:
type: integer
notnull: true
name:
type: string(50)
notnull: true
campaign_id:
type: integer
notnull: false
template_id:
type: integer
notnull: false
relations:
Command:
local: command_id
foreign: id
type: many
cascade: [delete]
Campaign:
local: campaign_id
foreign: id
cascade: [delete]
Template:
local: template_id
foreign: id
Template:
columns:
id:
primary: true
unique: true
type: integer
notnull: true
autoincrement: true
name:
type: string(50)
notnull: true
body:
type: string(255)
notnull: true
Samuel | 03/01/10 at 8:05pm
| Edit
(2) Possible Answers Submitted...
-
Last edited:
03/02/10
7:09pmGert Findel says:I entered the following dql query and worked OK
./symfony doctrine:dql "from Rule r, r.Xcommands c, c.Xevents e, e.Template t "
That means getXcommands() and getXevents() should work properly.
I did some changes to your schema
Organization:
columns:
id:
primary: true
unique: true
type: integer
notnull: true
autoincrement: true
sf_guard_user_id:
type: integer(4)
name:
unique: true
type: string(255)
notnull: true
first_name:
type: string(255)
last_name:
type: string(255)
phone:
type: string(255)
email:
type: string(255)
logo:
type: string(255)
country:
type: string(255)
is_active:
default: true
type: boolean
notnull: true
indexes:
IX_Organization_1:
fields: [is_active]
IX_Organization_2:
fields: [id]
relations:
sfGuardUser:
foreign: id
local: sf_guard_user_id
owningSide: true
type: one
foreignType: one
Campaign:
columns:
id:
primary: true
unique: true
type: integer
notnull: true
autoincrement: true
organization_id:
type: integer
notnull: true
name:
unique: false
type: string(255)
notnull: true
description:
type: blob(0)
is_active:
default: true
type: boolean
notnull: true
indexes:
IX_Campaign_1:
fields: [id]
IX_Campaign_2:
fields: [is_active]
relations:
Organization:
local: organization_id
foreign: id
cascade: [delete]
Campaign:
foreign: campaign_id
local: id
type: many
Subscriber:
columns:
id:
primary: true
unique: true
type: integer
notnull: true
autoincrement: true
first_name:
type: string(255)
last_name:
type: string(255)
mobile:
type: string(20)
phone:
type: string(255)
email:
type: string(255)
country:
type: string(255)
is_active:
default: true
type: boolean
notnull: true
campaign_id:
type: integer
relations:
Campaign:
local: campaign_id
foreign: id
Message:
columns:
id:
primary: true
unique: true
type: integer
notnull: true
autoincrement: true
parent_id:
type: integer
notnull: false
sms_message_id:
type: string(30)
notnull: false
campaign_id:
type: integer
direction:
type: enum
values: [Inbound,Outbound]
keyword:
type: string(20)
msg_from:
type: string(20)
msg_to:
type: string(20)
msg_contents:
type: string(255)
msg_option:
type: string(255)
server_address:
type: string(255)
status:
type: enum
values: [Pending,Delivered,Processed,Failed]
response_type:
type: enum
values: [Normal,Unknown]
result_code:
type: string(255)
msg_option:
type: string(255)
data:
type: string(255)
delivery_type:
type: enum
values: [SMS,Email]
carrier:
type: string(100)
network_type:
type: enum
values: [gsm,cdma,tdma,iden]
received_at:
type: timestamp
relations:
Campaign:
local: campaign_id
foreign: id
Message:
local: parent_id
foreign: id
# Rules !
Session:
actAs: { Timestampable: ~ }
columns:
id:
primary: true
unique: true
type: integer
notnull: true
autoincrement: true
campaign_id:
type: integer
notnull: false
msg_from:
type: string(30)
notnull: true
subscriber_id:
type: integer
notnull: false
msg_contents:
type: string(255)
notnull: false
expires_at:
type: timestamp
is_active:
default: true
type: boolean
notnull: true
relations:
Campaign:
local: campaign_id
foreign: id
cascade: [delete]
Subscriber:
local: subscriber_id
foreign: id
type: many
cascade: [delete]
Rule:
columns:
id:
primary: true
unique: true
type: integer
notnull: true
autoincrement: true
type:
type: enum
values: [User,System]
campaign_id:
type: integer
notnull: false
msg_to:
type: string(20)
notnull: false
msg_from:
type: enum
values: [Anyone,Outbound]
description:
type: string(255)
notnull: false
is_active:
default: true
type: boolean
notnull: true
relations:
Campaign:
local: campaign_id
foreign: id
type: one
class: Campaign
foreignAlias: Rules
cascade: [delete]
Command:
columns:
id:
primary: true
unique: true
type: integer
notnull: true
autoincrement: true
rule_id:
type: integer
notnull: true
command:
type: string(160)
notnull: false
relations:
Rule:
local: rule_id
class: Rule
foreign: id
foreignAlias: Xcommands
cascade: [delete]
Event:
columns:
id:
primary: true
unique: true
type: integer
notnull: true
autoincrement: true
command_id:
type: integer
notnull: true
name:
type: string(50)
notnull: true
campaign_id:
type: integer
notnull: false
template_id:
type: integer
notnull: false
relations:
Command:
local: command_id
foreign: id
type: many
foreignAlias: Xevents
cascade: [delete]
Campaign:
local: campaign_id
foreign: id
cascade: [delete]
Template:
local: template_id
foreign: id
Template:
columns:
id:
primary: true
unique: true
type: integer
notnull: true
autoincrement: true
name:
type: string(50)
notnull: true
body:
type: string(255)
notnull: true
If not, do you have a fixture for testing?- 03/01/10 9:53pm
Gert Findel says:BTW: I added the foreignaliases and did little changes.
PS: I believe you dont need the Campaign relation in the Campaign model. - 03/02/10 12:17am
Samuel says:I did the changes you suggested (except i used the plural form Events and Commands, instead of Xevent...) but it still doesn't work quite as it should.
Below is my complete fixsture.
Specifically on this section... I would expect to fill in this like this since command to event is many to many.. but it gives an error
Example of how i would expect it to work:
Rule:
rule_1:
type: Admin
campaign_id: c1
msg_to: 32075
msg_from: Anyone
description: Comply with anti-spam
Commands:
command_1:
command: Remove
...
Event:
event1:
Command: command_1
event2:
Command: command_1
Full fixture:
sfGuardUser:
sgu_admin:
username: admin
password: admin
is_super_admin: true
Organization: y
sgu_client:
username: smorhaim
password: mysecret
is_super_admin: false
Organization: x
Organization:
x:
id: 2
sf_guard_user_id: 2
name: SMLPro LLC
first_name: Samuelito
last_name: Lipovich
phone: 7865478190
email: samuel.morhaim@gmail.com
y:
id: 1
sf_guard_user_id: 1
name: ZizzerDog
first_name: Samuel
last_name: Morhaim
phone: 7865478180
email: sammy@zizzerdog.com
Campaign:
c1:
id: 1
Organization: x
name: 20 Off
description: 20 Off with Code
is_active: true
c2:
id: 2
Organization: x
name: Special Sale
description: Special Sale Offer
is_active: true
c3:
id: 3
Organization: y
name: 20 Off
description: 20 Off with Code
is_active: true
c4:
id: 4
Organization: x
name: 30 Off
description: 30 Off with Code
is_active: false
Subscriber:
s1:
id: 1
Campaign: c1
first_name: Samuel
last_name: Morhaim
mobile: 7865478190
country: 1
s2:
id: 2
Campaign: c1
first_name: Sara
last_name: Lipovich
mobile: 3054503930
country: 1
Rule:
rule_1:
type: Admin
campaign_id: 1
msg_to: 32075
msg_from: Anyone
description: Comply with anti-spam
Commands:
command_1:
command: Remove
rule_2:
type: User
campaign_id: 1
msg_to: 32075
msg_from: Anyone
description: See if the user replies the word RED or BLUE
Commands:
command_red:
command: Red
command_blue:
command: Blue
Event:
rule1remove:
Command: command_1
name: Remove
Campaign: c1
rule1reply:
command_id: 1
name: Reply
Template: removal_template
eventRed:
command_id: 2
name: Add
Campaign: c1
eventRedReply:
command_id: 2
name: Reply
Template: red_template
Template:
removal_template:
name: Removal Template
body: You have been removed from our list.
red_template:
name: Red Template
body: Thank you for Red
Session:
s1:
campaign_id: 1
msg_from: 32075
subscriber_id: 1
msg_contents: 1
sfGuardPermission:
sgp_admin:
name: admin
description: Administrator permission
sgp_client:
name: client
description: Regular Client permission
sfGuardGroup:
sgg_admin:
name: admin
description: Administrator group
sgg_client:
name: client
description: Client group
sfGuardGroupPermission:
sggp_admin:
sfGuardGroup: sgg_admin
sfGuardPermission: sgp_admin
sggp_client:
sfGuardGroup: sgg_client
sfGuardPermission: sgp_client
sfGuardUserGroup:
sgug_admin:
sfGuardGroup: sgg_admin
sfGuardUser: sgu_admin
sgug_client:
sfGuardGroup: sgg_client
sfGuardUser: sgu_client
- 03/02/10 8:24am
Gert Findel says:To make Event (N)--(N)Command I did this
./symfony doctrine:dql "from Rule r, r.Commands c, c.Events e, e.Template t " - 03/02/10 3:37pm
Gert Findel says:fixture:
Organization:
x:
id: 2
sf_guard_user_id: 2
name: SMLPro LLC
first_name: Samuelito
last_name: Lipovich
phone: 7865478190
email: samuel.morhaim@gmail.com
y:
id: 1
sf_guard_user_id: 1
name: ZizzerDog
first_name: Samuel
last_name: Morhaim
phone: 7865478180
email: sammy@zizzerdog.com
Campaign:
c1:
id: 1
Organization: x
name: 20 Off
description: 20 Off with Code
is_active: true
c2:
id: 2
Organization: x
name: Special Sale
description: Special Sale Offer
is_active: true
c3:
id: 3
Organization: y
name: 20 Off
description: 20 Off with Code
is_active: true
c4:
id: 4
Organization: x
name: 30 Off
description: 30 Off with Code
is_active: false
Subscriber:
s1:
id: 1
Campaign: c1
first_name: Samuel
last_name: Morhaim
mobile: 7865478190
country: 1
s2:
id: 2
Campaign: c1
first_name: Sara
last_name: Lipovich
mobile: 3054503930
country: 1
Rule:
rule_1:
type: Admin
campaign_id: 1
msg_to: 32075
msg_from: Anyone
description: Comply with anti-spam
Commands:
command_1:
command: Remove
rule_2:
type: User
campaign_id: 1
msg_to: 32075
msg_from: Anyone
description: See if the user replies the word RED or BLUE
Commands:
command_red:
command: Red
command_blue:
command: Blue
Event:
rule1remove:
name: Remove
Campaign: c1
rule1reply:
name: Reply
Template: removal_template
eventRed:
name: Add
Campaign: c1
eventRedReply:
name: Reply
Template: red_template
CommandEvent:
ce1:
Command: command_1
Event: rule1remove
ce2:
Command: command_1
Event: rule1reply
Template:
removal_template:
name: Removal Template
body: You have been removed from our list.
red_template:
name: Red Template
body: Thank you for Red
Session:
s1:
campaign_id: 1
msg_from: 32075
subscriber_id: 1
msg_contents: 1 - 03/02/10 3:39pm
Gert Findel says:Schema:
Organization:
columns:
id:
primary: true
unique: true
type: integer
notnull: true
autoincrement: true
sf_guard_user_id:
type: integer(4)
name:
unique: true
type: string(255)
notnull: true
first_name:
type: string(255)
last_name:
type: string(255)
phone:
type: string(255)
email:
type: string(255)
logo:
type: string(255)
country:
type: string(255)
is_active:
default: true
type: boolean
notnull: true
indexes:
IX_Organization_1:
fields: [is_active]
IX_Organization_2:
fields: [id]
relations:
sfGuardUser:
foreign: id
local: sf_guard_user_id
owningSide: true
type: one
foreignType: one
Campaign:
columns:
id:
primary: true
unique: true
type: integer
notnull: true
autoincrement: true
organization_id:
type: integer
notnull: true
name:
unique: false
type: string(255)
notnull: true
description:
type: blob(0)
is_active:
default: true
type: boolean
notnull: true
indexes:
IX_Campaign_1:
fields: [id]
IX_Campaign_2:
fields: [is_active]
relations:
Organization:
local: organization_id
foreign: id
cascade: [delete]
Campaign:
foreign: campaign_id
local: id
type: many
Subscriber:
columns:
id:
primary: true
unique: true
type: integer
notnull: true
autoincrement: true
first_name:
type: string(255)
last_name:
type: string(255)
mobile:
type: string(20)
phone:
type: string(255)
email:
type: string(255)
country:
type: string(255)
is_active:
default: true
type: boolean
notnull: true
campaign_id:
type: integer
relations:
Campaign:
local: campaign_id
foreign: id
Message:
columns:
id:
primary: true
unique: true
type: integer
notnull: true
autoincrement: true
parent_id:
type: integer
notnull: false
sms_message_id:
type: string(30)
notnull: false
campaign_id:
type: integer
direction:
type: enum
values: [Inbound,Outbound]
keyword:
type: string(20)
msg_from:
type: string(20)
msg_to:
type: string(20)
msg_contents:
type: string(255)
msg_option:
type: string(255)
server_address:
type: string(255)
status:
type: enum
values: [Pending,Delivered,Processed,Failed]
response_type:
type: enum
values: [Normal,Unknown]
result_code:
type: string(255)
msg_option:
type: string(255)
data:
type: string(255)
delivery_type:
type: enum
values: [SMS,Email]
carrier:
type: string(100)
network_type:
type: enum
values: [gsm,cdma,tdma,iden]
received_at:
type: timestamp
relations:
Campaign:
local: campaign_id
foreign: id
Message:
local: parent_id
foreign: id
# Rules !
Session:
actAs: { Timestampable: ~ }
columns:
id:
primary: true
unique: true
type: integer
notnull: true
autoincrement: true
campaign_id:
type: integer
notnull: false
msg_from:
type: string(30)
notnull: true
subscriber_id:
type: integer
notnull: false
msg_contents:
type: string(255)
notnull: false
expires_at:
type: timestamp
is_active:
default: true
type: boolean
notnull: true
relations:
Campaign:
local: campaign_id
foreign: id
cascade: [delete]
Subscriber:
local: subscriber_id
foreign: id
type: many
cascade: [delete]
Rule:
columns:
id:
primary: true
unique: true
type: integer
notnull: true
autoincrement: true
type:
type: enum
values: [User,System]
campaign_id:
type: integer
notnull: false
msg_to:
type: string(20)
notnull: false
msg_from:
type: enum
values: [Anyone,Outbound]
description:
type: string(255)
notnull: false
is_active:
default: true
type: boolean
notnull: true
relations:
Campaign:
local: campaign_id
foreign: id
type: one
class: Campaign
foreignAlias: Rules
cascade: [delete]
Command:
columns:
id:
primary: true
unique: true
type: integer
notnull: true
autoincrement: true
rule_id:
type: integer
notnull: true
command:
type: string(160)
notnull: false
relations:
Rule:
local: rule_id
class: Rule
foreign: id
foreignAlias: Commands
cascade: [delete]
Events: { foreignAlias: Commands, class: Event, refClass: CommandEvent }
Event:
columns:
id:
primary: true
unique: true
type: integer
notnull: true
autoincrement: true
name:
type: string(50)
notnull: true
campaign_id:
type: integer
notnull: false
template_id:
type: integer
notnull: false
relations:
Commands: { foreignAlias: Events, class: Command, refClass: CommandEvent }
Campaign:
local: campaign_id
foreign: id
cascade: [delete]
Template:
local: template_id
foreign: id
CommandEvent:
columns:
event_id: { type: integer, primary: true }
command_id: { type: integer, primary: true }
relations:
Event: { foreignAlias: Events }
Commands: { foreignAlias: Commands }
Template:
columns:
id:
primary: true
unique: true
type: integer
notnull: true
autoincrement: true
name:
type: string(50)
notnull: true
body:
type: string(255)
notnull: true
I just noticed I hadnt post the complete answer...
The solution I expose allows the N to N relation as you want
- 03/01/10 9:53pm
-
Last edited:
03/02/10
3:42amAlexandr Sidorov says:Hello, I was try create project with your schema.
1. You was specify "refClass: CampaignRules", but this is for many-to-many relations. Do you what this relation type? I think no.
2. Use Rule->Command->Event->Template without S
p.s. If you want use Rule->Commands you can add this to your schema:
Rule:
.....
relations:
Commands:
class: Command
Previous versions of this answer: 03/02/10 at 3:42am
- 03/02/10 11:23am
Samuel says:Yes,
Rule to campaign is many to many
Command to event is a many to many.
Event to template is one to many
A campaign may have many rules, each rule can have several commands, each command can have several events links to one template.
- 03/02/10 1:43pm
Alexandr Sidorov says:Are you clearly understand many to many relations.
"Rule to campaign is many to many" mean:
A campaign may have many rules and rule have many campaigns.
And one to many it mean:
A campaign may have many rules and rule should related with one campaign.
About your problem (SPECIFIC PROBLEM:...), as I told, you use objects with "S" in end, but it was not declared. You can use "class:" for relation, or change your code.
Also, what error do you have in output?
- 03/02/10 1:48pm
Alexandr Sidorov says:Rule->Command->Event->Template
instead
Rule->Commands->Events->Templates
plural words using in Propel, in Doctrine you should write relations as is.
- 03/02/10 11:23am
This question has expired.
Current status of this question: Completed
