leftJoin with postCreate

Restful Web Services

I have a database setup with a common user table, which stores usernames and passwords, and seperate user group tables for specific information for the group type.

I want to be able to create, for example, a new client user type and store the username and password in the user table and the specific client information in the client table. I use the column user_id to link the user table to the client table.

So, I am using postCreate to update the client table with the newly created id from the user table.

The following is working but seems flakey because I am only using a where statement based on email address because I haven't been able to find a way to get the newly created id from the joined user table.

                ->on( 'postCreate', function ( $editor, $id, $values, $row ) {
                    
                    // common log
                    insert_log( $editor->db(), 'create', $id, $values );
                   
                    // get data from user table
                    $data = array();
                    $data['cms_module_feusers_users'] = $editor->db()
                                ->query('select')
                                ->table('cms_module_feusers_users')
                                ->get('id')
                                ->where('username', $values['cms_module_feusers_users']['username'])
                                ->exec()
                                ->fetchAll();
                     
                    // update client table with newly added user id           
                    $editor->db()
                        ->query('update', 't_clientowner')
                        ->set('user_id', $data['cms_module_feusers_users']['0']['id'])
                        ->where('iClientOwnerId', $id)
                        ->exec(); 
                                               
                })

Is there a better way to do this? Can I access last_insert_id from my joined table during the postCreate event?

I thought it could be more robust if I used,

                        ->validator( Validate::unique(
                            ValidateOptions::inst()->message( 'Email address already in use' )                        
                        )), 

However, I don't seem to be able to check the email address is unique because it is on the joined table?

Any pointers greatly appreciated!

Thanks

Chris

Restful Web Services

Okay, I have now corrected my code with respect to the Validate::unique function and I can now successfully check for a unique email address within the joined table, well except the message doesn't show at the moment as that isn't quite right yet.

                        ->validator( DataTables\Editor\Validate::unique($cfg, username, cms_module_feusers_users, $db,
                            DataTables\Editor\ValidateOptions::inst()->message( 'Email address must be unique' )
                        )), 

However, I am still of a mind that their is a more robust way to properly update the original record using some kind of last insert id?

Cheers

allan

The following is working but seems flakey because I am only using a where statement based on email address because I haven't been able to find a way to get the newly created id from the joined user table.

If you have included cms_module_feusers_users.id (or whatever the field name is) in your list of fields, that should be available inside the postCreate event handler, so you wouldn't need to look it up again.

Allan

Restful Web Services

Hi Allan,

Thanks for your response, the support for datatables is really very good.

I get a null value in the response, see below.

{"DT_RowId":"row_8579","t_clientowner":{"iClientOwnerId":"8579","dSysDate":"19\/03\/2018 12:44","dSysDate_timestamp":1521463492,"iCountryId":"0","user_id":"0","sEmail":"","iOwnerId":"0","sType":"cli","sAddress":"Address","sBankFr":null,"sBankUk":null,"sComments":"","iUserId":"0"},"cms_module_feusers_users":{"id":null,"status_id":null,"username":null,"first_name":"-","last_name":"-","telephone":"-","expires":null},"cms_module_feusers_belongs":{"groupid":null}}

My form contains a hidden input <input type="hidden" name="data[0][cms_module_feusers_users][id]" /> which if removed means that the leftJoin database create fails, i.e. the entry into the table cms_module_feusers_users is not written.

Seems to me like the data returned is based on the hidden input value which is null and not on the newly created item and thus no data is returned?

Does that make sense or am I doing it wrong?

Thanks

Chris

allan

Can you show me how you define that field please? What should happen is that the data in $values will be what is sent to the client-side. If the id column is null, it suggests that there hasn't been a successful left join. What field is the left join done on?

Allan

Restful Web Services

Hi Allan,

Just to confirm. The leftjoin method I have works perfectly for a view and for an edit when both the cms_module_feusers_users and `t_clientowner``` records have previously been created. The join works fine because I have a value for the join.

When trying to create a new record if I remove the input <input type="hidden" name="data[0][cms_module_feusers_users][id]" value="" /> the new record is not inserted into the cms_module_feusers_users table but is in the main table t_clientowner.

If I add the input <input type="hidden" name="data[0][cms_module_feusers_users][id]" value="" /> the record is inserted into both the cms_module_feusers_users table and the t_clientowner table but the data returned to the client for the join is null and thus the cms_module_feusers_users.id value is not available.

Should I be able to create a new record in a leftjoin table without the use of this hidden input? The hidden input cannot have a value yet as the records have not yet been written to the database.

Chris

Restful Web Services

I have also been trying to get the unique check to work properly and although it now works correctly on create it does not work on edit. I receive the following error:

SELECT  `username` as 'username' FROM  `cms_module_feusers_users` WHERE `username` = :where_0 AND `t_clientowner`.`iClientOwnerId` != :where_1

error
An SQL error occurred: SQLSTATE[42S22]: Column not found: 1054 Unknown column 't_clientowner.iClientOwnerId' in 'where clause' ```

The column iClientOwnerId is in the parent table and the column username is in the joined table.

It looks to me like the column iClientOwnerId will not be available in the above query because it does not join the table t_clientowner, is this correct?

Can I modify the below to make it work correctly?

->validator( DataTables\Editor\Validate::unique($cfg, username, cms_module_feusers_users, $db)), 

Thanks

Chris

allan

Thanks - I'm with you now.

Unfortunately, currently, no. Doing an insert into a joined table is possible and it will work, but the pre-built libraries will always do an insert on the main table before doing any of the joined tables. Therefore the joined table's new row id would never be available at the time of the insert to the main table.

I can absolutely see why that would be useful and I've added it to my list of enhancements that it would be useful to specify if the left join insert should happen before or after the main table. I'm not sure when that will be implemented though. At the moment, you'd need to write into the joined table use a server-side event and then update the id value that was submitted with the newly created id.

For the validation, since you are pulling in data from a joined table you would need to use a custom validator for that. The default unique validator will only consider data in a single table.

Regards,
Allan

Restful Web Services

Hi Allan. Thanks for your reply. I am glad I know I cannot do it and I am not just making a mistake with my code. I have done as you suggested and implemented a custom validator. I have made it a reusable function like so:

function validate_cms_module_feusers_users_username ( $db, $data ) {

    // select data
    $validate = array();
    $validate = $db->query( 'select' )
        ->table( 'cms_module_feusers_users' )
        ->get( 'id' )
        ->where( 'username', $data['cms_module_feusers_users']['username'], '=' )
        ->and_where( 'id', $data['cms_module_feusers_users']['id'], '!=' )
        ->exec()
        ->fetchAll();
    
    return $validate;                        
    
}

And then I can call it from within my datatables code like so:

                        ->validator( function ( $val, $data, $field, $host ) use ( $db ) {
                            $validate = validate_cms_module_feusers_users_username ( $db, $data );                    
                            return strlen( $validate['0']['id'] ) > 0 ? 'Email address must be unique'  : true;
                        }),  

Which seems to work okay now.

Thanks for your help

Chris