Ask your Symfony questions! Pay money and get answers fast! (more info)

Correct my Schema Symfony

  • SOLVED

The following schema almost works perfectly as I would like it... but it has a problem.

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

Answers (2)

2010-03-01

Gert Findel answers:

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?


Gert Findel comments:

BTW: I added the foreignaliases and did little changes.

PS: I believe you dont need the Campaign relation in the Campaign model.


Samuel comments:

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: [email protected]

y:
id: 1
sf_guard_user_id: 1
name: ZizzerDog
first_name: Samuel
last_name: Morhaim
phone: 7865478180
email: [email protected]

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


Gert Findel comments:

To make Event (N)--(N)Command I did this

<code>./symfony doctrine:dql "from Rule r, r.Commands c, c.Events e, e.Template t "


Gert Findel comments:

fixture:

Organization:
x:
id: 2
sf_guard_user_id: 2
name: SMLPro LLC
first_name: Samuelito
last_name: Lipovich
phone: 7865478190
email: [email protected]

y:
id: 1
sf_guard_user_id: 1
name: ZizzerDog
first_name: Samuel
last_name: Morhaim
phone: 7865478180
email: [email protected]

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


Gert Findel comments:

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

2010-03-02

Alexandr Sidorov answers:

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


Samuel comments:

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.



Alexandr Sidorov comments:

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?


Alexandr Sidorov comments:

Rule->Command->Event->Template
instead
Rule->Commands->Events->Templates

plural words using in Propel, in Doctrine you should write relations as is.