How to use insert_string query helper with SQL function?

My database is using UUIDs as a primary key. When I insert into the DB (mariaDB), I need to do:

insert into table_name (id, parent_id, name,... etc. )
values (UUID_TO_BIN(UUID()), 'a UUID', 'record name', .etc)

I would like to use CI's insert_string function, but this array:

$data = array(
    'id' => 'UUID_TO_BIN(UUID())',
    'name' => 'record name',
    'parent_id' => 'UUID_TO_BIN(' . $parent_id . ')'
);

$this->db->insert_string('table_name',$data);

...I do not think will work, because each result is escaped, so CI will escape the whole text including the function, instead of only what is inside the UUID_TO_BIN function in the parent_id value.

I am trying to figure out if this is a possibility for the parent_id to run the function given. Otherwise, I guess the easiest way is to do the conversion to BIN from HEX in PHP, but will that break the SQL?

Solution

You could use the set() method, which accept optional third parameter ($escape), that will prevent data from being escaped if set to FALSE on the id column.

$data = array(
    // 'id' => 'UUID_TO_BIN(UUID())',
    'name' => 'record name',
    'parent_id' => 'UUID_TO_BIN(' . $parent_id . ')'
);

//set id column value as UUID
$this->db->set('id', 'UUID_TO_BIN(UUID())', FALSE);

$this->db->insert_string('table_name', $data);