Issue with 1 - n combination

nessinits

I use the code underneath to create a 1-n join

        ->join(
            Mjoin::inst( 'to_dossiers' )
                ->link( 'to_activities.activities_id', 'to_act_dos.activities_id' )
                ->link( 'to_dossiers.dossiers_id', 'to_act_dos.dossiers_id' )
                ->order( 'name asc' )
                ->fields(
                    Field::inst( 'dossiers_id' )
                        ->options( Options::inst()
                        ->table( 'to_dossiers' )
                        ->value( 'dossiers_id' )
                        ->label( 'name' )
                        ),
                    Field::inst( 'name' )
            )

All my tables have two unique columns (id as an integer and tablename_id as a varchar). As the example above I use the varchar to link the tables, but this results in records with the to_act_dos.activities_id filled to_activities.id instead of to_activities.activities_id.

Does anyone know what I am doing wrong, or is this a bug (or hidden feature). ;-)

Kind regards,
nessinits

allan

Do you mean that your primary key for the column is a compound key?

If you enable Editor's debug mode (add ->debug(true) before the ->process(...) statement), it will include the SQL statements it uses in the JSON return. Can you show me that JSON return?

Thanks,
Allan

nessinits

Hi Allan,

No it's not a compound key. I have two separate columns with unique identifiers. The first an integer ('legacy') "id" auto_increment as primary key and the second a varchar "table_id" with a unique generated uuid as a unique key for security purpose.

When you use integer values people can simply try to use different integer values to retrieve data. I use a generated uuid instead, so this is made more difficult.

In the mjoin I use the 'table_id', but somewhere in the core of the PHP the "table_id" is overwritten by the "id". At least that's what I think. I'll give your ->debug(true) a go.

Kind regards,
nessinits

nessinits

As you can see, I don't use to_activities.id, but to_activities.activities_id. However in the INSERT of to_act_dos the value of to_activities.id is placed into to_act_dos.activities_id.

{"data":[{"DT_RowId":"row_1","to_activities":{"activities_id":"03f344f0-19b6-11e8-9166-39281c182369","code":"20180008","foreign_id":"","options_id":"","status_id":"","applications_id":"","name":"Test","planned":"2018-02-24","completed":null,"follow":"","report":"","important":"","urgent":""},"to_persons":{"abbr":null},"options":{"name":null},"status":{"name":null},"follow":{"name":null},"report":{"name":null},"important":{"name":null},"urgent":{"name":null},"to_dossiers":[]}],"debugSql":[{"query":"SELECT  * FROM  `to_activities` WHERE `to_activities`.`id` = :where_0 ","bindings":[{"name":":where_0","value":"1","type":null}]},{"query":"UPDATE  `to_activities` SET  `activities_id` = :activities_id, `code` = :code, `foreign_id` = :foreign_id, `options_id` = :options_id, `status_id` = :status_id, `applications_id` = :applications_id, `name` = :name, `planned` = :planned, `completed` = :completed, `follow` = :follow, `report` = :report, `important` = :important, `urgent` = :urgent WHERE `to_activities`.`id` = :where_0 ","bindings":[{"name":":activities_id","value":"03f344f0-19b6-11e8-9166-39281c182369","type":null},{"name":":code","value":"20180008","type":null},{"name":":foreign_id","value":"","type":null},{"name":":options_id","value":"","type":null},{"name":":status_id","value":"","type":null},{"name":":applications_id","value":"","type":null},{"name":":name","value":"Test","type":null},{"name":":planned","value":"2018-02-24","type":null},{"name":":completed","value":null,"type":null},{"name":":follow","value":"","type":null},{"name":":report","value":"","type":null},{"name":":important","value":"","type":null},{"name":":urgent","value":"","type":null},{"name":":where_0","value":"1","type":null}]},{"query":"DELETE FROM  `to_act_dos` WHERE `activities_id` = :where_0 ","bindings":[{"name":":where_0","value":"1","type":null}]},{"query":"INSERT INTO  `to_act_dos`  ( `activities_id`, `dossiers_id` ) VALUES (  :activities_id,  :dossiers_id )","bindings":[{"name":":activities_id","value":"1","type":null},{"name":":dossiers_id","value":"e7129dbb-19b2-11e8-9166-39281c182369","type":null}]},{"query":"INSERT INTO  `to_act_dos`  ( `activities_id`, `dossiers_id` ) VALUES (  :activities_id,  :dossiers_id )","bindings":[{"name":":activities_id","value":"1","type":null},{"name":":dossiers_id","value":"08fd9ede-19b3-11e8-9166-39281c182369","type":null}]},{"query":"SELECT  `to_activities`.`id` as 'to_activities.id', `to_activities`.`activities_id` as 'to_activities.activities_id', `to_activities`.`code` as 'to_activities.code', `to_activities`.`foreign_id` as 'to_activities.foreign_id', `to_persons`.`abbr` as 'to_persons.abbr', `to_activities`.`options_id` as 'to_activities.options_id', `options`.`name` as 'options.name', `to_activities`.`status_id` as 'to_activities.status_id', `status`.`name` as 'status.name', `to_activities`.`applications_id` as 'to_activities.applications_id', `to_activities`.`name` as 'to_activities.name', `to_activities`.`planned` as 'to_activities.planned', `to_activities`.`completed` as 'to_activities.completed', `to_activities`.`follow` as 'to_activities.follow', `follow`.`name` as 'follow.name', `to_activities`.`report` as 'to_activities.report', `report`.`name` as 'report.name', `to_activities`.`important` as 'to_activities.important', `important`.`name` as 'important.name', `to_activities`.`urgent` as 'to_activities.urgent', `urgent`.`name` as 'urgent.name' FROM  `to_activities` LEFT JOIN `to_persons` ON `to_activities`.`foreign_id` = `to_persons`.`persons_id`  LEFT JOIN am_options as options ON `to_activities`.`options_id` = `options`.`options_id`  LEFT JOIN am_options as status ON `to_activities`.`status_id` = `status`.`options_id`  LEFT JOIN `to_applications` ON `to_activities`.`applications_id` = `to_applications`.`applications_id`  LEFT JOIN am_options as follow ON `to_activities`.`follow` = `follow`.`options_id`  LEFT JOIN am_options as report ON `to_activities`.`report` = `report`.`options_id`  LEFT JOIN am_options as important ON `to_activities`.`important` = `important`.`options_id`  LEFT JOIN am_options as urgent ON `to_activities`.`urgent` = `urgent`.`options_id` WHERE `to_activities`.`id` = :where_0 ","bindings":[{"name":":where_0","value":"1","type":null}]},{"query":"SELECT DISTINCT  `to_activities`.`activities_id` as 'dteditor_pkey', `to_dossiers`.`dossiers_id` as 'dossiers_id', `to_dossiers`.`name` as 'name' FROM  to_activities as to_activities  JOIN `to_act_dos` ON `to_activities`.`activities_id` = `to_act_dos`.`activities_id`   JOIN `to_dossiers` ON `to_dossiers`.`dossiers_id` = `to_act_dos`.`dossiers_id`  ORDER BY `name`  asc ","bindings":[]},{"query":"SELECT DISTINCT  `dossiers_id` as 'dossiers_id', `name` as 'name' FROM  `to_dossiers` ","bindings":[]},{"query":"INSERT INTO  `am_logging`  ( `tablename`, `table_id`, `action`, `requestvalues`, `datavalues`, `username` ) VALUES (  :tablename,  :table_id,  :action,  :requestvalues,  :datavalues,  :username )","bindings":[{"name":":tablename","value":"to_activities","type":null},{"name":":table_id","value":"1","type":null},{"name":":action","value":"edit","type":null},{"name":":requestvalues","value":"{\"action\":\"edit\",\"data\":{\"row_1\":{\"to_activities\":{\"activities_id\":\"03f344f0-19b6-11e8-9166-39281c182369\",\"code\":\"20180008\",\"name\":\"Test\",\"planned\":\"2018-02-24\",\"applications_id\":\"\",\"options_id\":\"\",\"status_id\":\"\",\"foreign_id\":\"\",\"completed\":\"\",\"follow\":\"\",\"report\":\"\",\"important\":\"\",\"urgent\":\"\"},\"to_dossiers\":[{\"dossiers_id\":\"e7129dbb-19b2-11e8-9166-39281c182369\"},{\"dossiers_id\":\"08fd9ede-19b3-11e8-9166-39281c182369\"}],\"to_dossiers-many-count\":\"2\"}},\"PHPSESSID\":\"971f0a5b9a285469c24179a16cf0c562\"}","type":null},{"name":":datavalues","value":"{\"to_activities\":{\"activities_id\":\"03f344f0-19b6-11e8-9166-39281c182369\",\"code\":\"20180008\",\"name\":\"Test\",\"planned\":\"2018-02-24\",\"applications_id\":\"\",\"options_id\":\"\",\"status_id\":\"\",\"foreign_id\":\"\",\"completed\":\"\",\"follow\":\"\",\"report\":\"\",\"important\":\"\",\"urgent\":\"\"},\"to_dossiers\":[{\"dossiers_id\":\"e7129dbb-19b2-11e8-9166-39281c182369\"},{\"dossiers_id\":\"08fd9ede-19b3-11e8-9166-39281c182369\"}],\"to_dossiers-many-count\":\"2\"}","type":null},{"name":":username","value":"admin","type":null}]}]} 

allan

I see - yes. The libraries are expecting the primary key value (defined by the ->pkey() method - defaults to id) from the main table to be used. Let me look into that and get back to you.

Thanks,
Allan

allan

I think I know what it is - Editor is struggling with the default parameter of id and resolving it when related to the table name of the tables table.

I don't know what your Editor::inst() constructor, but could you try adding the table name to the pkey field (optional third parameter) - e.g.

Editor::inst( $db, 'tableName', 'tableName.id' )

Thanks,
Allan

nessinits

It was

$table  = 'to_activities';  
$editor =   Editor::inst( $db, $table);
                ->pkey($table.'.id');

I changed it to:

$table  = 'to_activities';  
$editor =   Editor::inst( $db, $table, $table.'.id');

The result is the same ...