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

Warning: Please do not give out any FTP or ssh credentials to anyone, unless you trust them completely. Giving out login details is dangerous.

If the asker does not get an answer then they have 10 days to request a refund.

$15
Many-to-Many relationship, Doctrine and sfGuardUser

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:

500 | Internal Server Error | Doctrine_Query_Exception
...
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:
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


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

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)

What would be the correct configuration to get this done?

This question has been answered.

Jonathan | 02/20/11 at 8:14am Edit


The experts have suggested, on average, a prize of $15 for this question.

(1) Responses

See a threaded view of answers?

Warning: Please do not give out any FTP or ssh credentials to anyone, unless you trust them completely. Giving out login details is dangerous.

  • avatar
    Last edited:
    02/21/11
    4:34am
    Gergely Szilagyi says:

    Hi!

    I will post 3 possible solution :

    ( 1 ) - rummage in DB ( yah ... :S )

    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.


    ( 2 ) - take care about the table definitions!


    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



    ( 3 ) be careful

    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.

    Previous versions of this answer: 02/20/11 at 11:24am | 02/20/11 at 11:26am | 02/20/11 at 11:27am

This question has expired.





Current status of this question: Completed



Warning: Please do not give out any FTP or ssh credentials to anyone, unless you trust them completely. Giving out login details is dangerous.

If the asker does not get an answer then they have 10 days to request a refund.