MJoin issue with link table

edowood

Hi,

I'm encountering an issue when using MJoin on a link table. I have a link table with two columns and a composite primary key. The SQL for that is here:

CREATE TABLE prog_dir_xref (
   program_id number(25,0)  NOT NULL,
   director_id number(25,0)  NOT NULL,
   CONSTRAINT prog_dir_xref_pk PRIMARY KEY (program_id,director_id)
) ;

Note that I'm on Oracle 11.2.

The link table is between two other tables, functions and users, and the two columns in the link table are the PKs for those tables. They're foreign keyed and all and I don't think that's a database issue. I am also using a view to swap the user_id for a fullname, which is reflected in my code. A query to represent that table is here:

select program_id, fullname from prog_dir_xref
join personnel_names on prog_dir_xref.director_id = personnel_names.user_id;

So in my javascript file I have:

{
                "label": "* Program Director:",
                "name": "personnel_names[].fullname",
                "type": "select",
                "multiple": "true"
},

And in my PHP file I have this for the select field:

            ->join(
                Mjoin::inst('personnel_names')
                    ->link('function.function_id', 'prog_dir_xref.program_id')
                    ->link('personnel_names.user_id', 'prog_dir_xref.director_id')
                    //->order('fullname asc')
                    ->field(
                        Field::inst('fullname')
//                            ->validator('Validate::dbValues')
                            ->validator('Validate::notEmpty')
                            ->options(Options::inst()
                                ->table('personnel_names')
                                ->value('user_id')
                                ->label('fullname')
                            ->where(function ($q) {
                                $q->where('personnel_role_id', '4');
                            })
                            )
                    )
            )

What I want to happen is the select field gives the user a list of director names. The user can select 1+ directors and submit. This adds these records to the link table, saving by id. Right now I have two records in the link table.

+------------+-------------+
| PROGRAM_ID | DIRECTOR_ID |
+------------+-------------+
|         62 |        1189 |
|         62 |        1301 |
+------------+-------------+

My select field works ok, i.e. it shows the options. The issue is when I submit it fails. The relevant part of my debug sql is here:

{"query":"DELETE FROM  prog_dir_xref WHERE program_id = :where_0 ","bindings":[{"name":":where_0","value":"62","type":null}]},{"query":"INSERT INTO  prog_dir_xref  ( program_id, director_id ) VALUES (  :program_id,  :director_id )","bindings":[{"name":":program_id","value":"62","type":null},{"name":":director_id","value":null,"type":null}]}]}

So it looks like it's deleting fine. But the binding for the director_id is null, causing the insert to fail. I can't figure out why that part doesn't work. Any advice? I've looked through my frontend code to check that the IDs are correct and stuff, trying to make sure the bindings work. But I'm worried it's something with the way the Editor core works. I saw some other answers here about issues with multi-column PKs.

Thanks for any help.

allan

I think that this:

"name": "personnel_names[].fullname",

Should actually be:

"name": "personnel_names[].user_id",

You'd also need to add a another field for user_id to the fields list of the MJoin. When those changes, should it still not work, could you show me the data that is being sent to the server on edit (from the "Headers" tab in the "Network" panel for that request).

Thanks,
Allan

edowood

Terrific Allan, that worked for me! I was just getting the data names confused. I did verify that it worked with Oracle's 2 column PK as well. Thanks a ton for your help, you do a great job running your site and answering user questions! If we hadn't already bought Editor we'd buy it now!

allan

Thanks . Great to hear that does the business for you.

The fact that the join table has a compound key shouldn't actually make any difference here. The tricky bit is if the main table has a compound key.

Allan