How do I alias primary table name
How do I alias primary table name
greaterebiz
Posts: 8Questions: 5Answers: 0
Using the editor:inst function, I would like to associate an alias with the primary table name. For the most part the following code works great.
switch ($action) {
case 'controllers_datatable':
// Build our Editor instance and process the data coming from _POST
Editor::inst($db, $this->shape_access_controllers_m->ftn(). ' as sac ')
->fields(
Field::inst('sac.facilities_id'),
Field::inst('sac.access_points_id')->validator('Validate::notEmpty')
->options($this->shape_access_points_m->ftn().' as sap', 'id', 'name', function($q) {
$q->where('sap.facilities_id', $this->input->post('facilities_id',true));
}),
Field::inst('sac.ssh_port_num')->validator('Validate::notEmpty'),
Field::inst('sac.ssh_username')->validator('Validate::notEmpty'),
Field::inst('sac.ssh_password')->validator('Validate::notEmpty'),
Field::inst('sac.mysql_username')->validator('Validate::notEmpty'),
Field::inst('sac.mysql_password')->validator('Validate::notEmpty'),
Field::inst('sac.mac_address')->validator('Validate::notEmpty'),
Field::inst('sac.enabled')->validator('Validate::notEmpty')
// Field::inst('fac.city'),
// Field::inst('fac.state_abbr'),
// Field::inst('acc.name')->set(false)
)
//->leftJoin ( $this->shape_facilities_m->ftn().' as fac ', 'facilities_id','=','fac.id') // later gator
//->leftJoin ( $this->shape_access_points_m->ftn().' as sap ', 'access_points_id','=','sap.id') // later gator
->where ('sac.facilities_id',$this->input->post('facilities_id',true))
->process($_POST)
->json();
break;
}
}
That is it works find if I do an update operation, but if I do a create operation, I get an SQL error, "SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'as sac ( facilities_id, access_points_id, ssh_port_num, ssh_username, `' at line 1".
It appears, that my trick of using appending ' as sac ' to the table name in the editor::inst function is not the correct thing to do. Again it works find when I update and exiting record, but I cannot create a new record.
Answers
That sounds like a bug in Editor I'm afraid. What version is it that you are using? If not 1.5.5 could you update please?
I'm afraid that at the moment it means that the workaround is to not use an alias (which I presume is just to make a shorthand string to access the table?).
Allan
Thanks Alan. I am using 1.5.5. I have worked around the problem using the table name instead of an alias, e.g. $this->shape_access_controllers_m->ftn().'.ssh_port_num'. Now everything works fine. It appears there is somewhere in the editor code that the insert just list the values instead of using a set clause. Thus you get a sql statement like insert into table.sac (enabled, ....). Apparently this is also true for an update as well.
Still - Datatables and Editor are two really helpful products saving me loads of time now that I know how to work around this particular problem.
Good to hear the workaround works! I'll dig into this and try to get it resolved. I doubt it will be for 1.5.6 I'm afraid, but the following version (which might be 1.6).
Thanks,
Allan
I'm having the same issue in 1.6 where I can't do any CRUD operations on an mssql DB, but can successfully receive the initial $_POST/GET
Could you provide a few more details such as the code you are using please?
Thanks,
Allan