FuelPHP Forums

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!