Mjoin options with linked self-referencing table

paravis

Hi there, we are using Mjoin to join groups of items using a link table.

Here is our server code:

    Editor::inst( $db,'form_items','id')
        ->fields(
            Field::inst('form_items.id'),
            Field::inst('form_items.item_order')->validator('Validate::numeric'),
            Field::inst('form_items.form_id'),
            Field::inst('form_items.value')->validator('Validate::notEmpty')->validator('Validate::maxLen',array('max'=>36)),
            Field::inst('form_items.name')->validator('Validate::maxLen',array('max'=>72))
        )
        ->join(
            Mjoin::inst('form_items')
                ->name('members')
                ->aliasParentTable('fi')
                ->link('fi.id','form_item_xref.collection_id')
                ->link('form_items.id','form_item_xref.member_id')
                ->fields(
                    Field::inst('id')
                        ->options(function(){
                            global $page;
                            global $db;
                            return $db->sql('SELECT name AS label,id AS value,collection,CASE WHEN collection = 0 THEN \'Available Items\' ELSE \'Available Collections\' END AS class FROM form_items WHERE page_id = '.$page.';')->fetchAll();
                        }),
                    Field::inst('name')
                )
            )
        ->where('form_items.page_id',$page)
        ->process( $_POST )
        ->json();

The resulting JSON data has our DataTables/Editor field listed as 'members[].id', though the available options are still showing to be from 'form_items[].id'.

We saw a forum post that covered this same topic, but we get "undefined is not an object (evaluating 'json.options')" when initializing the DataTable using the following code ajax source, columns, buttons etc have been removed):

    formItemTable = $('#formItemTable').DataTable({
        initComplete:function(e,settings,json){
            formItemEditor.field('members[].id').update(json.options['form_items[].id']);
        }
    });

Since version 1.5.4, is there a proper way of doing this? Or do I have a type-o somewhere?

Much thanks!

~Laz

paravis

Well I solved my own issue here ...

My problem is that I am reloading the ajax data multiple times depending on the selection of other items in other tables or dropdown menus.

I was receiving the "json.options undefined" error because the initialization of the DataTable was receiving nothing from the first ajax response, since other dependencies had not yet been selected.

So the fix was to add this JS function to any events that change the ajax URL and data.

function updateFieldOpts() {
    formItemEditor.field('members[].id').update(formItemTable.ajax.json().options['form_items[].id']);
};

Thanks!

paravis

One last comment for anyone dealing with this same task --

We now have the field update function firing on the DataTables 'xhr' event. This is working perfectly.

allan

Hi,

Thanks for posting back. Sorry I didn't get a chance to respond earlier, but great to hear you've got it working as you need now!

Regards,
Allan

paravis

Oh, no worries Allan. But I do have another question related to this particular issue ...

When creating a new entry, it's coming back with a SQL error:

SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 1"

Here is the SQL from the PHP libraries:

INSERT INTO  form_items
  ( form_id, page_id, collection,
  collection_type_id, header_text,
  primary_item_id, item_type_id,
  value, name, info, placeholder,
  label_attached, item_width,
  label_width, item_offset, cover_item,
  selection_list_id, readonly,
  validation, number_validation, required,
  active, existing_value )
VALUES
  (  :form_id,  :page_id,  :collection,
  :collection_type_id,  :header_text,
  :primary_item_id,  :item_type_id,
  :value,  :name,  :info,  :placeholder,
  :label_attached,  :item_width,
  :label_width,  :item_offset,  :cover_item,
  :selection_list_id,  :readonly,
  :validation,  :number_validation,  :required,
  :active,  :existing_value )

INSERT INTO  form_item_xref
  ( collection_id, member_id )
VALUES
  (  :collection_id,  :member_id )

INSERT INTO  form_item_xref
  ( collection_id, member_id )
VALUES
  (  :collection_id,  :member_id )

It seems the error is coming from the second and third insert query, as there is technically no "collection_id" field -- it should be the ID returned from the first insert query.

However, looking at the database, there is no record inserted at all from the first query. Am I missing something?

(Also, FYI, these fields, except for "collection_id" are all in our PHP Editor instance, I had previously removed them from my first post to save space.)

Thanks!

paravis

Just tested without any of the linked data going in, and the same error occurs. So it seems like the problem is happening in the first insert query actually.

Here is the data being sent to the server:

{
  "action":"create",
  "data":{
    "0":{
      "form_items":{
        "form_id":"44",
        "page_id":"120",
        "value":"",
        "collection":"1",
        "name":"Testing",
        "collection_type_id":"34",
        "item_type_id":"",
        "cover_item":"0",
        "item_width":"5",
        "item_offset":"0",
        "label_attached":"0",
        "label_width":"4",
        "required":"0",
        "readonly":"0",
        "placeholder":"",
        "info":"",
        "selection_list_id":"",
        "existing_value":"",
        "validation":"",
        "number_validation":"",
        "header_text":"",
        "primary_item_id":"",
        "active":1
      },
      "members":[],
      "members-many-count":0
    }
  }
}

Any thoughts?

paravis

Well, I got this fixed, but I am still very confused as to the reason why this is happening.

In the preSubmit event, I modify the data to add a few POST variables for the server script. In the select statement immediately following the insert, not all but a few of these variables are blank.

I'm sure it is my own user error, so I have done a workaround on the server side script to find those variables elsewhere in the POST data array, and that is what fixed this particular issue.

Am I missing another event that happens after the successful submit, when Editor is re-selecting the data that was inserted, where I should also be modifying the request data? I would expect the request to get this information from the create action, but I guess it does not.

Also, another effect that the missing variable has is that the SQL statement is constructed with a few of the 'table_names' missing the underscore between the words. For example, 'table_names' becomes 'tablenames'. But after fixing the missing POST variable, this no longer happens.

If there is something that I should be doing better, or more properly, please let me know. Thank you!

allan

It sounds like you might need to add another (hidden?) field for the collection id to be submitted to the server. The data above doesn't appear to actually be submitted any joined information - just the main table. is that expected there?

Allan

paravis

Yes we already have the hidden field, which was retrieving the value for any items created, since their array position was always [0].

Now, when modifying, the array position could be anything. So that is where we were having the problem.

In order to deal with this, and I'm sure I am doing this the wrong way, I am simply modifying the Editor as follows after other variables change (in this case, form and page):

editor.s.ajax.data={table:'items',forms:form,page:page};

The DataTables API to modify ajax URL is documented and easy to find. But I could not find any API information to modify the ajax URL or data for Editor. Would this be the correct way to accomplish this task?

paravis

Realistically, I guess I could just cycle through each position of the array, but I was looking for a method that involved less overhead.

In this case, I believe modifying the ajax request is our best option.

allan

I would suggest using ajax.data rather than the internal settings object for this (which is considered to be private).

It sounds like the loop option might be the best method at the moment I'm afraid.

Allan

paravis

Ok sounds good.

Thanks for your advice Allan!