Can't use alias for primary table name

greaterebiz

I'm using editor version 1.6.3 of datatables in a php application using MySql.

I created an editor instance in the following ajax code and because there are joins to main table with duplicate field name, assigned an alias to the main table.

public function ajax($action)
{
global $db;
switch ($action) {
case "inventory_user_facility_locations_datatable": {
$franchisees_id = $this->input->post('franchisees_id', true);
$action = null; //$this->input->post('action',true);

            $editor = Editor::inst($db, **$this->shape_pos_inventory_count_locations_m->ftn() 
                                                      . ' as loc'**, 'id')
                    ->fields(
                        Field::inst('loc.id as id'),
                        Field::inst('loc.system_defined as system_defined'),
                        Field::inst('loc.purpose as purpose'),
                        Field::inst('loc.short_name as short_name'),
                        Field::inst('loc.enabled as enabled'),
                        Field::inst('loc.franchisees_id as franchises_id'),
                        Field::inst('loc.facilities_id as facilities_id'),
                        Field::inst('fran.id as fran_id_from_facility')
                    )
                    ->leftJoin($this->shape_facilities_m->ftn() . ' as facs', 'facs.id', '=', 'loc.facilities_id')
                    ->leftJoin($this->shape_franchisees_m->ftn() . ' as fran', 'facs.franchise_id', '=', 'fran.id')
                    // where we find user define facility locations for this franchisee
                    ->where('fran.id', $franchisees_id)// don't user franchisee_id field, use looked up franchisee_id
                    ->where('loc.scope', 'facility')// facility locations
                    ->where('loc.system_defined', 0);    // defined by user
            }

Most everything works fine until I do a create operation and then I get this SQL error:

An SQL error occurred: 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 loc ( purpose, short_name, enabled, facilities_id ) VALUES ( 'Vendi' at line 1<br />

I finally realized the problem is an INSERT INTO statement is being user and it does not support and alias name. Am I doing something wrong or is this problem in datatables. For now I have worked around the problem by first checking to see if the action is "create" and if it is instantiate and editor that does not use an alias and fortunately, no joins are needed

public function ajax($action)
{
global $db;
switch ($action) {

            case "inventory_user_facility_locations_datatable": {
            $franchisees_id = $this->input->post('franchisees_id', true);
            $action = null; //$this->input->post('action',true);

            // this is a kluge because when doing a create, you can't have an alias name for the table
            // that requires an SQL  INSERT INTO table statement and it does not support an alias.
            // Fortunately for a create we don't need to join any other tables so an alias is not needed

            if (!is_null($action) && $action=='create') {
                $editor = Editor::inst($db, $this->shape_pos_inventory_count_locations_m->ftn(), 'id')
                    //->debug(true)
                    ->fields(
                        Field::inst('id'),
                        Field::inst('system_defined'),
                        Field::inst('scope'),
                        Field::inst('purpose'),
                        Field::inst('short_name'),
                        Field::inst('enabled'),
                        Field::inst('franchisees_id'),
                        Field::inst('facilities_id')
                    );
            }
            else {
                // if its not a create operation, we can alias and this is required since we are
                // joining tables with duplicate field names.
                $editor = Editor::inst($db, $this->shape_pos_inventory_count_locations_m->ftn() . ' as loc', 'id')
                    //->debug(true)
                    ->fields(
                        Field::inst('loc.id as id'),
                        //Field::inst('loc.system_defined as system_defined'),
                        Field::inst('loc.purpose as purpose')
                        /*
                        // create options list for purpose enumerations
                        ->options(
                            Options::inst()
                                ->table($this->shape_pos_inventory_count_locations_m->ftn())
                                ->value('purpose')
                                ->label('purpose')
                        )
                        */
                        ,
                        Field::inst('loc.short_name as short_name'),
                        Field::inst('loc.enabled as enabled'),
                        Field::inst('loc.franchisees_id as franchises_id'),
                        Field::inst('loc.facilities_id as facilities_id'),
                        Field::inst('fran.id as fran_id_from_facility')
                    )
                    ->leftJoin($this->shape_facilities_m->ftn() . ' as facs', 'facs.id', '=', 'loc.facilities_id')
                    ->leftJoin($this->shape_franchisees_m->ftn() . ' as fran', 'facs.franchise_id', '=', 'fran.id')
                    // where we find user define facility locations for this franchisee
                    ->where('fran.id', $franchisees_id)// don't user franchisee_id field, use looked up franchisee_id
                    ->where('loc.scope', 'facility')// facility locations
                    ->where('loc.system_defined', 0);    // defined by user
            }

Surely, I'm just doing something stupid. I've used datatables for at least 5 years now and never run into this problem.

allan

It would be worth updating to Editor 1.9.6 as 1.6 is really quite old now. You can down load 1.9.6 from here. Make sure you update both the client side and server side libraries.

Allan