⚠ In case you've missed it, we have migrated to our new website, with a brand new forum. For more details about the migration you can read our blog post for website migration. This is an archived forum. ⚠

  •     

profile picture

Error in setRelation when two relations point to same table and both use Where clause



Mugs321
  • profile picture
  • Member

Posted 24 June 2018 - 18:07 PM

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
  • profile picture
  • Administrator
  • 1,166 posts

Posted 10 July 2018 - 10:36 AM

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
  • profile picture
  • Administrator
  • 1,166 posts

Posted 11 July 2019 - 12:53 PM

 

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