Many to many combo box issue on edit

edited June 2017 in Modules
I have created a many to many relationship using a custom lookup table (ie not Fuel's relationships table). Within the CMS record for the candidate table, the multi select appears OK, with the left hand textarea populated with the correct records in the related table. Selecting a record into the right hand textarea, and saving, creates the correct foreign and candidate keys in the lookup table. But... on editing the record, the right hand box does not show the saved relationships.

I should declare that the models for all 3 tables (candidate, lookup, foreign) are using the 'dsn' property to point to a remote database, if that should make any difference.

So the has_many property looks like:
public $has_many = array( 'authorities' => array( // this model has many 'authorities' 'model' => array('app' => 'authorities_model'), // the lookup table 'relationships_model' => 'authority_installation_model', // the foreign key of the 'has many' table in lookup table 'foreign_key' => 'authority_id', // this table's primary key in the lookup table 'candidate_key' => 'installation_id' )

What might be preventing the records showing in the right hand combo when saved?


  • edited 7:12AM
    Hmm... the values are loaded in from the form_fields method on the model. The MY_Model::form_fields() method is where it tries to get the values for the relationship (around line 2878). Are values coming through for you there?
  • edited June 2017
    Well, there was an error that was being suppressed that I didn't notice at 1st. The immediate query before this seems to be:
    SELECT `authority_installation`.* FROM `authority_installation` WHERE `candidate_table` = 'installations' AND `foreign_table` = 'authorities' AND `authority_installation`.`installation_id` = '65'
    My lookup table doesn't have foreign_table or candidate_table columns. When I add them, naturally, there's no problem
  • edited June 2017
    Presumably the lookup table must reflect the fuel relationships table? The lookup table is actually modelling a Laravel pivot / lookup table, so adding the Fuel relationships columns candidate_table and foreign_table, whilst not drastic, seems unnecessary. Is there a way of configuring my way out of this? The lookup table is named by convention to be a relationship between the authorities and installations tables. I suppose I can configure the table with default values for these columns.
  • edited 7:12AM
    With those extra fields, the fuel_relationships table allows you to have only one table to manage lookups instead of creating a table for each lookup relationship like in Laravel. I've done something similar in Eloquent using the following method on the model which essentially uses 4 columns instead of 2 for the relationship lookup:
    /** * Returns a belongsToMany relationship object using a shared table. * Handy if you don't like creating pivot tables for every belongsToMay relationship. * * @return Illuminate\Database\Eloquent\Relations\BelongsToMany */ public function sharedRelationship($model, $tableInfo = null) { if (is_string($model)) { $foreignModel = $model; $model = new $model; } else { $foreignModel = get_class($model); } $defaultTableInfo = [ 'table' => (defined('static::SHARED_RELATIONSHIPS_TABLE') ? static::SHARED_RELATIONSHIPS_TABLE : 'fuel_relationships'), 'key_column' => 'key', 'table_column' => 'table', 'foreign_key_column' => 'foreign_key', 'foreign_table_column' => 'foreign_table' ]; $tableInfo = (empty($tableInfo)) ? $defaultTableInfo : array_merge($defaultTableInfo, $tableInfo); $belongsToMany = $this->belongsToMany($foreignModel, $tableInfo['table'], $tableInfo['key_column'], $tableInfo['foreign_key_column']) ->wherePivot('table', $this->getTable()) ->wherePivot('foreign_table', $model->getTable()) ; return $belongsToMany; }
Sign In or Register to comment.