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

Many-to-Many relationship, Doctrine and sfGuardUser Symfony

  • SOLVED

Hello,

I'm trying to configure a many-to-many relationship with Doctrine. It unfortunately doesn't work as expected.

Expected behavior:

$user = sfGuardUserTable::getInstance()->findOneById(2);
$contact = ContactTable::getInstance()->findOneByExtId(282723);
$user->Contacts[] = $contact;
$user->save();
//Contact id 282723 now belongs to user id 2


However when executing this I get:

<strong>500 | Internal Server Error | Doctrine_Query_Exception</strong>
...
stack trace
at Doctrine_Query->query('FROM Contact.UserContact WHERE Contact.UserContact.id IN (?)', array('2'), null)
in SF_ROOT_DIR/lib/vendor/symfony/lib/plugins/sfDoctrinePlugin/lib/vendor/doctrine/Doctrine/Connection.php line 897 ...
...


I'm struggling to get this relationship working, here is my YAML file:

sfGuardUser:
actAs:
Timestampable:
columns:
id:
primary: true
type: integer(9)
autoincrement: true
...
attributes:
export: all
validate: true
indexes:
is_active_idx:
fields: [is_active]

Contact:
actAs:
Timestampable:
columns:
id:
primary: true
type: integer(9)
autoincrement: true
...
indexes:
IX_ContactExtID:
fields: [contact_ext_id]
type: unique
relations:
Users:
foreignAlias: Contacts
class: sfGuardUser
local: contact_id
foreign: user_id
refClass: UserContact

UserContact:
columns:
user_id:
type: integer(9)
primary: true
contact_id:
type: integer(9)
primary: true
relations:
Contact:
foreignAlias: UserContacts
sfGuardUser:
local: user_id
foreign: id
foreignAlias: UserContacts



I tried many different variants of the YAML file, but none worked.

If I leave the linking table UserContact as suggested in Symphony:

UserContact:
columns:
user_id:
type: integer(9)
primary: true
contact_id:
type: integer(9)
primary: true
relations:
Contact:
foreignAlias: UserContacts
sfGuardUser:
foreignAlias: UserContacts

I get the following error when loading the SQL into DB:
<em> SQLSTATE[42000]: Syntax error or access violation: 1072 Key column 'sf_guard_user_id' doesn't exist in table. Failing Query: "CREATE TABLE user_contact (user_id BIGINT, contact_id BIGINT, INDEX sf_guard_user_id_idx (sf_guard_user_id), PRIMARY KEY(user_id, contact_id)) ENGINE = INNODB". Failing Query: CREATE TABLE user_contact (user_id BIGINT, contact_id BIGINT, INDEX sf_guard_user_id_idx (sf_guard_user_id), PRIMARY KEY(user_id, contact_id)) ENGINE = INNODB
</em>

If specify foreign and local as they really are:

UserContact:
columns:
user_id:
type: integer(9)
primary: true
contact_id:
type: integer(9)
primary: true
relations:
Contact:
foreignAlias: UserContacts
sfGuardUser:
local: user_id
foreign: user_id
foreignAlias: UserContacts

<em> SQLSTATE[HY000]: General error: 1005 Can't create table './criticr/#sql-147a7_315.frm' (errno: 150). Failing Query: "ALTER TABLE user_contact ADD CONSTRAINT user_contact_user_id_sf_guard_user_user_id FOREIGN KEY (user_id) REFERENCES sf_guard_user(user_id)". Failing Query: ALTER TABLE user_contact ADD CONSTRAINT user_contact_user_id_sf_guard_user_user_id FOREIGN KEY (user_id) REFERENCES sf_guard_user(user_id) </em>

What would be the correct configuration to get this done?

Answers (1)

2011-02-20

Gergely Szilagyi answers:

Hi!

I will post 3 possible solution :

<strong> ( 1 ) - rummage in DB ( yah ... :S ) </strong>

First of all, please take care about unique and primary fields in your database.
None of them can be NULL in database.
So fix this in following steps:
in schema.yml for every unique or primary field :
notnull : true
then when you rebuilded this new schema, then watch the database in your admin ( for example : PhpMyAdmin ), and check every (unique, or primary) fields really are NULL : disabled
for this step i can help. When you tell me your DB store type or version.


If you have another SQL error , then :
you have schema.yml syntax error
or
already have records in DB with NULL value in unique fields. In this case fix deficiencies manually, or set table definition NOT-UNIQUE ( just for the succesfully build under maintenance! ), and fix the problem programatically, then set the table unique again.


<strong>( 2 ) - take care about the table definitions!</strong>


USER_CONTACT TABLE :

HM-HM not too lucky using this, in symfony , cause you have an admin generate module to admin this relations table, and symfony will die cause not clear primary key refferring in routing . I say, because  i had  a lot of trouble in the past from this „symfony recommended solution”:

Better to use , and mutch better adminable! I this case, you have an id field, and need just caring about the double key fields:



#The doctrine have no idea about witch table relation depend on user_id,
#unless „class” property if the names are not equals!!!

UserContact:
columns:
user_id:
type: integer(9)
notnull : true
contact_id:
type: integer(9)
notnull : true
indexes :
double_key_index :
fields: [contact_id, user_id]
type : unique
relations :
Contact:
foreignAlias: UserContacts
onDelete : CASCADE
sfGuardUser:
class : sfGuardUser
local : user_id
foreign : id
foreignAlias: UserContacts
onDelete : CASCADE



<strong>( 3 ) be careful</strong>

If you are sure about , you have a working schema in ORM and DB too, then use this code , when you are saving a relation :


$user = sfGuardUserTable::getInstance()->findOneById(2);
$contact = ContactTable::getInstance()->findOneByExtId(282723);


if($user and $contact)
{
//
// you know, you have both object

$relation = new UserContact();
$relation-> sfGuardUser = $user;
$relation → Contact = $contact;

try
{
$relation->save();
}
catch(Exception $e)
{
/// in this case you know :
/// already been one relation in DB with same double-key!
}

// anyway in this point, you can sure about , the relation is in DB from now!
}else
{
// oops, an object ( $user or $contact ) still not exist in DB
}

###########

I hope that you could help me with these problems occurred daily, and the solution is always at least one of the 3 options.