Love Fuel?    Donate

FuelPHP Forums

Ask your question about FuelPHP in the appropriate forum, or help others by answering their questions.
Can I save a self referencing relationship with one call to save()?
  • I have two types of users: professionals and their clients. I have a single users table for both, and they're connected to each other through a connections table (since I store other attributes about the relation, I can't use a strict many-to-many join table). If a professional wants to sign up as their own client (for a free trial of the site or something), can I save the new professional and their connection to themselves with one save() call? (I'm asking because I'd like to execute both statements in a transaction). Here are my tables...
    CREATE TABLE `users` (
     `id` MEDIUMINT(8) UNSIGNED NOT NULL AUTO_INCREMENT,
     `email` VARCHAR(256) NOT NULL,
     `pword` CHAR(64) NOT NULL,
     `salt` CHAR(32) NOT NULL,
     `name` VARCHAR(64) NOT NULL,
     `birthday` DATE NOT NULL,
     `timezone` VARCHAR(64) NOT NULL,
     `created_at` DATETIME NOT NULL,
     `archived_at` DATETIME NULL,
     `is_locked` TINYINT(1) NOT NULL DEFAULT 0,
     `is_confirmed` TINYINT(1) NOT NULL DEFAULT 0,
     PRIMARY KEY (id),
     UNIQUE INDEX (email)
    ) ENGINE=InnoDB;
    
    CREATE TABLE `connections` (
     `id` MEDIUMINT(8) UNSIGNED NOT NULL AUTO_INCREMENT,
     `prof_id` MEDIUMINT(8) UNSIGNED NOT NULL,
     `client_id` MEDIUMINT(8) UNSIGNED NOT NULL,
     `request_id` MEDIUMINT(8) UNSIGNED NOT NULL,
     `created_at` DATETIME NOT NULL,
     `archived_at` DATETIME NULL,
     PRIMARY KEY (`id`),
     INDEX (`prof_id`),
     INDEX (`client_id`),
     INDEX (`request_id`)
    ) ENGINE=InnoDB;
    
    Here are my models...
    class Model_User extends Orm\Model
    {
       protected static $_has_many = array(
            'client_connections' => array(
                'key_to' => 'prof_id',
                'model_to' => 'Model_Connection'
            ),
            'prof_connections' => array(
                'key_to' => 'client_id',
                'model_to' => 'Model_Connection'
            )
        );
    }
    
    class Model_Connection extends Orm\Model
    {
        protected static $_properties = array( 'prof_id', 'client_id', 'created_at' );
    
        protected static $_belongs_to = arary(
            'client' => array(
                'key_from' => 'client_id',
                'model_to' => 'Model_User',
                'key_to' => 'id'
            ),
            'prof' => array(
                'key_from' => 'prof_id',
                'model_to' => 'Model_User',
                'key_to' => 'id'
            )
        );
    }
    
    Here's my controller...
    class Controller_Profs extends Controller_Template 
    {
        public function action_register () {
            if ( Input::post() ) 
            {
                $user = new Model_User();
                $user->name = Input::post('name');
                $user->email = Input::post('email');
                $connection = new Model_Connection();
                $connection->client_id = $user->id;  // here's the self-referencing part
                $user->client_connections[] = $connection;
                $user->save();
            }
        }
    }
    
    Obviously, $user->id isn't set when $connection->client_id is assigned it above. Is there some sort of placeholder for the $user's id-to-be? I tried assigning by reference, but it throws an exception "Cannot assign by reference to overloaded object".
    $user = new Model_User();
    $user->name = Input::post('name');
    $user->email = Input::post('email');
    $connection = new Model_Connection();
    $connection->client_id =& $user->id;  // here's the self-referencing part
    $user->client_connections[] = $connection;
    $user->save();
    
    The obvious solution is to save the $user first, and then save the connection, for example...
    $user = new Model_User();
    $user->name = Input::post('name');
    $user->email = Input::post('email');
    $user->save();
    $connection = new Model_Connection();
    $connection->client_id = $user->id;  // here's the self-referencing part
    $user->client_connections[] = $connection;
    $user->save();
    
    However, I'm trying to use a transaction, because in reality, I'm also inserting several roles for the user and sending a confirmation email. If either fails, I'd like to roll everything back (I didn't post it all to keep from inundating readers with code). I'm sorry if this is a dumb question, but I've been scratching my head for a while. Happy Thanksgiving!
  • Since you're using the ORM, I think you'll have to create an Observer to create the Connection after the user is saved. It doesn't say this in the docs but if the Observer throws an Exception it will automatically (see edit) rollback the transaction. http://docs.fuelphp.com/packages/orm/observers/intro.html If you weren't using the ORM I would suggest looking at DB::start_transaction(), DB::commit_transaction(), and DB::rollback_transaction() found here: http://docs.fuelphp.com/classes/database/db.html#/method_start_transaction So, if the Observer doesn't work out for you, you may need to keep use these methods instead. EDIT:
    The rollback will only happen if save()'s second param is set to true.
  • Thanks Johnny. I realized after a little while that this was kind of a silly question (I'm embarassed about it haha). I figured out that I only wanted to enclose those operations in a transaction so I didn't want to have to remember to delete everything if something went wrong. However, with the beauty of ORM and cascade delete, I don't have to. If the email fails to send, I just delete the user, and the ORM's cascade delete takes care of its relations. Awesome! Since I don't have to have a transaction, the double save solution works fine. Thanks for the help! I didn't know the ORM automatically rolled back on exceptions. Good to know.
  • Thanks for the help! I didn't know the ORM automatically rolled back on exceptions. Good to know.

    It doesn't, though as of v1.1 the save() method has a second param that allows you to initiate a transaction for the entire save operation. [edit]Edited the above statement as I got the param number wrong[/edit]
  • Jelmer Schreuder wrote on Wednesday 30th of November 2011:
    Thanks for the help! I didn't know the ORM automatically rolled back on exceptions. Good to know.

    It doesn't, though as of v1.1 the save() method has a third param that allows you to initiate a transaction for the entire save operation.

    Jelmer is correct, it doesn't do this automatically (like I thought) but it's not the third param, it's the second.
    // orm/classes/model.php
    
    public function save($cascade = null, $use_transaction = false)
    {
         ...
    
         try
         {
                # do save stuff ...
         }
      catch (\Exception $e)
      {
          $use_transaction and $db->rollback_transaction();
          throw $e;
      }
    
      ...
     }
    

Howdy, Stranger!

It looks like you're new here. If you want to get involved, click one of these buttons!

In this Discussion