[Mugs321]

Hi there,

 

I'm having an issue when trying to set relations for two fields that are both foreign keys to the same lookup table. Running the code below as-is, I receive the following error:

 

Fatal error: Uncaught exception 'PDOException' with message 'SQLSTATE[42000]: Syntax error or access violation: 1066 Not unique table/alias: 'language'' in F:\Programming\Web\FR\www\GroceryCrud\libraries\zendframework\zend-db\src\Adapter\Driver\Pdo\Connection.php on line 360

 

 

The following code works when:

• I remove the N:N relationship, or
• I remove the second setRelation() call, or
• I remove the ['GameID' => $gameID] filter from one of the setRelation() calls

   

Seems like a bug to me.

 

My code:

include("libraries/autoload.php");

use GroceryCrud\Core\GroceryCrud;

$database = include('database.php');
$config = include('config.php');

$gameID = $_GET['GID']; 

$crud = new GroceryCrud($config, $database);

$crud->setTable('item');
$crud->setSubject('Item', 'Items');
//$crud->where(['item.GameID' => $gameID]);

$crud->setRelationNtoN('Used In Game(s)', 'game_uses_item', 'game', 'ItemID', 'GameID', 'game_shortcode', null, ["GameID" => $gameID]);


$crud->setRelation('ShortNameLanguageID','language','en', ['GameID' => $gameID]);
$crud->setRelation('LongNameLanguageID','language','en', ['GameID' => $gameID]);


$output = $crud->render();

if ($output->isJSONResponse) {
    header('Content-Type: application/json; charset=utf-8');
    echo $output->output;
    exit;
}


$css_files = $output->css_files;
$js_files = $output->js_files;
$output = $output->output;

include('view.php');

[web-johnny]

Hello @Mugs321,

 

This seems like a bug but to be honest it is the first time that someone is reporting this issue and it also seems very tricky from the development side. I will add it to the backlog and I will inform you once this is fixed.

 

At the meanwhile can I suggest a work-around? From the code I see that although you have two fields the user will basically always (or almost always?) choose the same language. Can I suggest for now (only as a work-around till this is fixed) to use the callbackBeforeUpdate and the callbackBeforeInsert to do something like this?

 

$crud->setRelation('ShortNameLanguageID','language','en', ['GameID' => $gameID]);
$crud->fieldType('LongNameLanguageID', 'hidden');
$callbackAddLanguageId = function ($stateParameters) {

    $stateParameters->data['LongNameLanguageID'] = $stateParameters->data['ShortNameLanguageID'];

    return $stateParameters;
};
$crud->callbackBeforeInsert($callbackAddLanguageId);
$crud->callbackBeforeUpdate($callbackAddLanguageId);

I hope this helped

Regards

Johnny

[web-johnny]

 

Hi there,

 

I'm having an issue when trying to set relations for two fields that are both foreign keys to the same lookup table. Running the code below as-is, I receive the following error:

 

Fatal error: Uncaught exception 'PDOException' with message 'SQLSTATE[42000]: Syntax error or access violation: 1066 Not unique table/alias: 'language'' in F:\Programming\Web\FR\www\GroceryCrud\libraries\zendframework\zend-db\src\Adapter\Driver\Pdo\Connection.php on line 360

 

 

The following code works when:

• I remove the N:N relationship, or
• 
  
• I remove the second setRelation() call, or
• 
  
• I remove the ['GameID' => $gameID] filter from one of the setRelation() calls
  

   

• 
  

Seems like a bug to me.

 

My code:

include("libraries/autoload.php");

use GroceryCrud\Core\GroceryCrud;

$database = include('database.php');
$config = include('config.php');

$gameID = $_GET['GID']; 

$crud = new GroceryCrud($config, $database);

$crud->setTable('item');
$crud->setSubject('Item', 'Items');
//$crud->where(['item.GameID' => $gameID]);

$crud->setRelationNtoN('Used In Game(s)', 'game_uses_item', 'game', 'ItemID', 'GameID', 'game_shortcode', null, ["GameID" => $gameID]);


$crud->setRelation('ShortNameLanguageID','language','en', ['GameID' => $gameID]);
$crud->setRelation('LongNameLanguageID','language','en', ['GameID' => $gameID]);


$output = $crud->render();

if ($output->isJSONResponse) {
    header('Content-Type: application/json; charset=utf-8');
    echo $output->output;
    exit;
}


$css_files = $output->css_files;
$js_files = $output->js_files;
$output = $output->output;

include('view.php');

I am glad to inform you that the issue with the setRelation bug is now solved on version 2.7.8

In case you are intrested you can download the latest version from here: https://www.grocerycrud.com/users/enterprise_latest_version

Please let me know in case you are still having issues

 

Regards

Johnny