Join tables - one-to-many join

mbrennand

I have 3 tables, im trying to join one to many but cant get me head around it. What am i doing wrong?

TABLES:
members - id, firstname, lastname etc

guns - id, name

member_guns - member, gun

JOIN ON:
member_guns.member = members.id

member_guns.gun = guns.id

<script type="text/javascript">
var editor; // use a global for the submit and return data rendering in the examples
 
$(document).ready(function() {
    editor = new $.fn.dataTable.Editor( {
        ajax: "database/connections/members.php",
        table: "#example",
        fields: [ {
                label: "First name:",
                name: "firstname"
            }, {
                label: "Last name:",
                name: "lastname"
            }, {
                label: "Address:",
                name: "address"
            }, {
                label: "FAC Number:",
                name: "fac"
            }, {
                label: "Membership:",
                name: "membership"
            }, {
                label: "Guns:",
                name: "guns[].id",
                type: "checkbox"
            }
        ]
    } );
 
    $('#example').DataTable( {
        dom: "Tfrtip",
        ajax: "database/connections/members.php",
        columns: [
            { data: null, render: function ( data, type, row ) {
                // Combine the first and last names into a single table field
                return data.firstname+' '+data.lastname;
            } },
            { data: "address" },
            { data: "fac" },
            { data: "membership" },
            { data: "guns", render: "[, ].name" }
        ],
        tableTools: {
            sRowSelect: "os",
            aButtons: [
                { sExtends: "editor_create", editor: editor },
                { sExtends: "editor_edit",   editor: editor },
                { sExtends: "editor_remove", editor: editor }
            ]
        }
    } );
} );
</script>



Editor::inst( $db, 'members' )
    ->fields(
        Field::inst( 'firstname' )->validator( 'Validate::notEmpty' ),
        Field::inst( 'lastname' )->validator( 'Validate::notEmpty' ),
        Field::inst( 'address' )->validator( 'Validate::notEmpty' ),
        Field::inst( 'fac' )->validator( 'Validate::notEmpty' ),
        Field::inst( 'membership' )->validator( 'Validate::notEmpty' )
    )
    ->join(
        Join::inst( 'guns', 'array' )
            ->join(
                array( 'id', 'gun' ),
                array( 'id', 'member' ),
                'member_guns'
            )
            ->fields(
                Field::inst( 'id' )->validator( 'Validate::required' ),
                Field::inst( 'name' )
            )
    )
    ->process($_POST)
    ->data();

allan

Hi,

I'm wondering if line 69 and 70 in the above should be swapped. The reason being is that the first parameter of join() should refer to the parent table (members in this case) and the second to the joined table (guns).

If you reverse them, does the data load as expected?

Thanks,
Allan

mbrennand

Thanks for the response. No this does not do anything, I get the same "invalid JSON response"

allan

Okay - what is the response that it is returning if it is invalid. The notes here explain how to view the response. Hopefully it will contain an error message explaining what is going on!

Allan

mbrennand

No error, the prompt says invalid JSON and I get a blank response from database/connections/members.php. The datatable just says loading....

http://www.matthewbrennand.co.uk/conrad/members.php

allan

I see - thanks for checking that. An empty return is invalid JSON which explains that part of it, but it doesn't explain why you are getting a blank return. Can you check the server's error logs? It sounds like it is configure to not show errors in the page, the error log should help.

Allan

mbrennand

the server shows errors. Is my editor script definitely right with all the joins?

SELECT members.id, firstname, lastname, address, fac, membership, GROUP_CONCAT(guns.name) as name

FROM member_guns 

INNER JOIN members ON member_guns.member = members.id

INNER JOIN guns ON member_guns.gun = guns.id

the above query displays what i want, but i need members to be the main table for the editor to create the members

allan

The Editor PHP and .NET libraries don't support SQL functions (GROUP_CONCAT for example), but the above shows raw SQL rather than the Editor setup.

Your PHP from the first post above looks okay, but obviously something is going wrong. What errors is the server showing?

Allan

mbrennand

working - something simple, missing:

Field::inst( 'id' )->validator( 'Validate::notEmpty' ),

Full Code

// Build our Editor instance and process the data coming from _POST
Editor::inst( $db, 'members' )
    ->fields(
        Field::inst( 'id' )->validator( 'Validate::notEmpty' ),
        Field::inst( 'firstname' ),
        Field::inst( 'lastname' ),
        Field::inst( 'address' ),
        Field::inst( 'fac' ),
        Field::inst( 'membership' )
    )
    ->join(
        Join::inst( 'guns', 'array' )
            ->join(
                array( 'id', 'member' ),
                array( 'id', 'gun' ),
                'member_guns'
            )
            ->fields(
                Field::inst( 'id' )->validator( 'Validate::required' ),
                Field::inst( 'name' )
            )
    )
    ->process($_POST)
    ->json();

Although the checkbox is not working in the editor

when i include

            ->fields(
                Field::inst( 'id' )->validator( 'Validate::required' )->options( 'guns', 'id', 'name' ),
                Field::inst( 'name' )
            )

I get - <b>Fatal error</b>: Call to undefined method DataTables\Editor\Field::options() in <b>/home/matthewb/public_html/conrad/database/connections/members.php</b> on line <b>36</b><br />

allan

The options method is new in v1.4 which is currently in beta. It sounds like you might be using the 1.3 libraries. The 1.4 beta can be downloaded from the Editor site.

Allan

mbrennand

ok thanks I have done this and its now working. How can I add a new option to the guns table from the member table editor instance. i.e.

If you gun is not here please manually type, then it will add it as a new row to the gun table

allan

That action isn't something that is directly available in Editor - it would likely require a custom field type to be created which can act as both a select and text input. The server-side script would also need to identify when an item needs to be created, rather than just referenced, and do that insert.

Regards,
Allan