Table updating when adding a new link

jaccort

Hi,

*** EDIT: Solved this, please don't waste any time on it!

Firstly, thanks to Allan for such a wonderful tool.

Secondly - apologies for such a long post!

I have a database with a link table. (design at the end of the post)

I want to be able to edit BrandSkillLinks, subject to a few rules:

• The representation displayed to the enduser is the nice, human-readable names, not the numeric IDs used in the link table.
• Edits made to the BrandSkillLinks table (whether as New, Edited or Deleted items) are reflected in the represented data immediately they're carried out.
• Each SkillNameID appears in the BrandSkillLinks table 0 or 1 times, and as such it should be impossible to choose a SkillName that's already in use. I've set up a view using a left outer join to achieve this and I'm pulling this in as the basis for the select list in the editor; this is called unbrandedSkills

Problems I have with my code:

• If I add a new entry to the BrandSkillLinks table, I have to refresh the page with F5 for it to appear in the DataTable. Editing or deleting entries show up straight away. Is there a reason for this or am I doing something horribly wrong?
• I'd like the list of unbrandedSkills presented in the editor for assignment to be updated immediately a skill/brand link is changed.

The obvious thing to do is call ajax.reload() as soon as any changes are made - which is exactly what I've tried to do at the end of my Javascript - however, the console.log line never appears in the JavaScript console, so it seems likely I've misunderstood how to do this.

Any pointers?

CREATE TABLE "Calls" (
    SkillID INTEGER,
    SkillName   VARCHAR(128) NOT NULL,
         ......
)

CREATE TABLE Brands(
    id INTEGER PRIMARY KEY,
    Name VARCHAR(128) NOT NULL,
    target INTEGER
)

CREATE TABLE BrandSkillLink (
    BrandID INTEGER,
    SkillID INTEGER,
    FOREIGN KEY(BrandID) REFERENCES Brands(ID),
    FOREIGN KEY(SkillID) REFERENCES Calls(SkillID)
)

CREATE VIEW unbrandedSkills  AS
    select calls.skillid, SkillName FROM calls
    LEFT OUTER JOIN brandSkillLink ON brandSkillLink.SkillID = calls.SkillID
    WHERE brandSkillLink.SkillID is null

    /*
        Brand/Skill split is a joined table 
    */
    
    var brandSkillEditor = new $.fn.dataTable.Editor( {
        ajax: 'php/table.brandSkill.php',
        table: '#brandSkillLink',
        fields: [
            {
                label: "Brand",
                name: "brandSkillLink.BrandID",
                type: "select"
            },
            {
                label: "Skill",
                name: "brandSkillLink.SkillID",
                type: "select"
            }
        ]
    } );
    
    var bsLinkTable = $('#brandSkillLink').DataTable( {
        dom: 'Bfrtip',
        ajax: 'php/table.brandSkill.php',
        columns: [
            { "data": "Calls.SkillName" },
            { "data": "brands.name" }
        ],
        select: true,
        buttons: [
            { extend: 'create', editor: brandSkillEditor },
            { extend: 'edit',   editor: brandSkillEditor },
            { extend: 'remove', editor: brandSkillEditor }
        ]
    } );    
    
    brandSkillEditor.on('postEdit', function(e, json, data) {
        console.log("reloading brandSkillEditor");
        brandSkillEditor.ajax.reload();
    });

// Build our Editor instance and process the data coming from _POST
Editor::inst( $db, 'brandSkillLink', 'skillID' )
    ->fields(
        Field::inst('Calls.SkillName'),
        Field::inst('brands.name'),
        Field::inst('brandSkillLink.BrandID')
            ->options('brands', 'id', 'name'),
        Field::inst('brandSkillLink.SkillID')
            ->validator( 'Validate::unique' )
            ->options('unbrandedSkills', 'skillID', 'skillName')
    )
    ->leftJoin('calls', 'calls.SkillID', '=', 'brandSkillLink.SkillID')
    ->leftJoin('brands', 'brands.id', '=', 'brandSkillLink.BrandID')
    ->process( $_POST )
    ->json();