Join tables - one-to-many join on 1 tables and one-to-one join on another

mbrennand

http://matthewbrennand.co.uk/conrad/attendance.php

I'm trying to create an editor instance for attendance to log which gun/s they are using.
I have most of it working but i am struggling with what to do next.

Table Structure:

gun table (fields) - id, name
member table (fields) - id, firstname, lastname
members_guns (fields) - id, member, gun
attendance table (fields) - id, member, gun, timestamp

In the datatable I would like members.firstname+members.lastname, guns.name, attendance.timestamp

Editor::inst( $db, 'attendance' )
    ->fields(
        Field::inst( 'id' )->validator( 'Validate::notEmpty' ),
        Field::inst( 'member' ),
        Field::inst( 'gun' ),
        Field::inst( 'attendance.timestamp' )
    )
    //->leftJoin( 'members', 'members.id', '=', 'attendance.member' )
    //->leftJoin( 'guns', 'guns.id', '=', 'attendance.gun' )
    ->join(
        Join::inst( 'guns', 'array' )
            ->join('gun', 'id')
            ->fields(
                Field::inst( 'id' )->validator( 'Validate::required' )->options( 'guns', 'id', 'name' ),
                Field::inst( 'name' )
            ),
        Join::inst( 'members', 'array' )
            ->join('member', 'id')
            ->fields(
                Field::inst( 'id' )->validator( 'Validate::required' )->options( 'members', 'id', 'firstname' ),
                Field::inst( 'firstname' ),
                Field::inst( 'lastname' )
            )
    )
    ->process($_POST)
    ->json();

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/attendance.php",
        table: "#example",
        fields: [ {
                label: "Member:",
                name: "members[].id",
                type: "select"
            }, {
                label: "Guns:",
                name: "guns[].id",
                type: "checkbox"
            }
        ]
    } );
 
    $('#example').DataTable( {
        dom: "Tfrtip",
        ajax: "database/connections/attendance.php",
        columns: [
            { data: "id" },
            { data: null, render: function ( data, type, row ) {
                // Combine the first and last names into a single table field
                return data.firstname+' '+data.lastname;
            } },
            { data: "guns", render: "[, ].name" },
            { data: "attendance.timestamp"}
        ],
        tableTools: {
            sRowSelect: "os",
            sSwfPath: "assets/DataTables-1.10.4/extensions/TableTools/swf/copy_csv_xls_pdf.swf",
            aButtons: [
                { sExtends: "editor_create", editor: editor },
                { sExtends: "editor_edit",   editor: editor },
                { sExtends: "editor_remove", editor: editor },
                {
                    sExtends: "collection",
                    sButtonText: "Save",
                    sButtonClass: "save-collection",
                    aButtons: [ 'copy', 'csv', 'xls', 'pdf' ]
                },
                'print'
            ]
        }
    } );
} );

tangerine

A. Your link doesn't work,
and B. You don't say what the problem is.

mbrennand

Sorry, link fixed. It does not group rows by member and there are errors when creating new record and also when editing.

New Record:

<br />
<b>Fatal error</b>:  Call to a member function insertId() on a non-object in <b>/home/matthewb/public_html/conrad/database/Editor/Editor.php</b> on line <b>1106</b><br />

Edit Record:

{"error":"SQLSTATE[42000]: Syntax error or access violation: 1110 Column 'id' specified twice"}

mbrennand

Any ideas?

allan

Hi,

Sorry I missed your thread before. Could you possibly reinstate the page at your link above once more so I can take a look?

Thanks,
Allan

mbrennand

ok, page now accessable

allan

Great thanks. The JSON returned for each row in the DataTable looks something like:

        {
            "DT_RowId": "row_1",
            "id": "1",
            "member": "2",
            "gun": "2",
            "attendance": {
                "timestamp": "2014-12-18 01:14:18"
            },
            "guns": [
                {
                    "id": "2",
                    "name": "Assault Rifle"
                }
            ],
            "members": [
                {
                    "id": "2",
                    "firstname": "Mat",
                    "lastname": "Brennand"
                },
                {
                    "id": "2",
                    "firstname": "Mat",
                    "lastname": "Brennand"
                }
            ]
        }

Could you clarify how you want this to display in the DataTable? You have a Name column, do you want that to display a concatenated list of all of the names in the members list? If so, then columns.render is the way to do it and loop over the members array, building up the string required.

Allan

mbrennand

I would like to display the members firstname and lastname together within the name column but i would also like to group the rows together. so if a member has used 3 guns on the same timestamp the it would display as ---- gun1,gun2,gun3

GROUP BY member, timestamp

allan

The Editor PHP libraries don't have a GROUP BY operation, however, if you flip it around a bit so that you are getting the data for the members at the top level and then join on the guns, you could use the array concatenation operator for columns.data to display the list of guns in a single column. For example: data: "guns[, ].name" (to display with a , separator).

Allan

mbrennand

Did not fully understand that.. I have changed to data: "guns[, ].name" and swapped the guns and members joins, but its still the same. Still undefined names and not joing in same row.

Also

Errors:
New Record:

<b>Fatal error</b>:  Call to a member function insertId() on a non-object in <b>/home/matthewb/public_html/conrad/database/Editor/Editor.php</b> on line <b>1106</b>

Edit Record:

{"error":"SQLSTATE[42000]: Syntax error or access violation: 1110 Column 'id' specified twice"}

allan

I think I need to step back for a moment and just confirm what you are looking for if that is okay - so I don't lead you down the garden path!

At the moment your code will show a single row for every attendance record. Within that record will be a a list of the guns used, and also the members who were present. They will be within that attendance row.

Is that what you want?

If so, are you able to mock up a table showing how you want it to be displayed?

Thanks,
Allan

mbrennand

For every attendance there should be a new line based on member and timestamp. Each row should only have one member but list multiple guns that have been used by that member on the given timestamp.

Name	Guns	When
Mat Barnes	Assault Rifle, Pistol	2014-12-18 01:14:18
Frank Taylor	Pistol, Sniper	2014-12-18 01:14:18
Mat Barnes	Pistol	2014-12-11 08:22:19

allan

I see - thanks for the clarification. That where we are having problems then as the Editor libraries will show only one row for every record in the host table (i.e. each attendance in this case, not attendance + member).

Unfortunately the Editor libraries do not have an option to display multidimensional data from multiple tables in this fashion at the moment. You would need to create a custom SQL query that would grab the data in the form that you want and use DataTables to display that.

Apologies for the initial misunderstanding of what you were looking for!

Allan

mbrennand

Ok, thank you for your time. Have you got a link to the documentation for creating the custom SQL query

allan

The database class sql() method can be used to create and execute custom SQL commands.

Allan

mbrennand

Thanks. I have now done this and the returned JSON is correct. But I cant get it to echo the data into the datatable.

http://matthewbrennand.co.uk/conrad/attendance.php

allan

You need to use ajax.dataSrc and set it to be an empty string if you are just returning a plain array of data points.

Allan

mbrennand

Im using this.....

$data = $db->sql( "
SELECT a.id, firstname, lastname, timestamp, GROUP_CONCAT(name
SEPARATOR  ', ')  as usedguns
FROM attendance AS a
INNER JOIN members AS m ON m.id = a.member
INNER JOIN guns AS g ON g.id = a.gun
GROUP BY m.id, a.timestamp
" )->fetchAll();
echo json_encode($data);
exit();

allan

Looks fine - you just need to use the ajax.dataSrc option in your DataTable initialisation as I mentioned, as set it to be an empty string. That tells DataTables to expect an array of data.

Allan

mbrennand

Thanks, but this did not work :(

allan

The page linked to above doesn't use ajax.dataSrc - it simply uses dataSrc, which is not a parameter DataTables knows about.

As per the ajax.dataSrc documentation examples you want something like:

$('#example').dataTable( {
  "ajax": {
    "url": "database/connections/attendance.php",
    "dataSrc": ""
  },
  ...
} );

Allan

mbrennand

Hi Allan,

I'm now struggling to insert data into this table.
Please see same link..... error - request unknown parameter "firstname"

Do i need something like this?
```
<?php

/*
* Example PHP implementation used for the index.html example
*/

// DataTables PHP library
include( "../DataTables.php" );

// Alias Editor classes so they are easy to use
use
DataTables\Editor,
DataTables\Editor\Field,
DataTables\Editor\Format,
DataTables\Editor\Join,
DataTables\Editor\Validate;

// Build our Editor instance and process the data coming from _POST
$data = $db->sql( "
SELECT a.id, firstname, lastname, timestamp, GROUP_CONCAT(name
SEPARATOR ', ') as usedguns
FROM attendance AS a
INNER JOIN members AS m ON m.id = a.member
INNER JOIN guns AS g ON g.id = a.gun
GROUP BY m.id, a.timestamp
" )->fetchAll();
echo json_encode($data);
exit();

if ( isset($_POST['action']) && $_POST['action'] === 'create' ) {
// Adding a new record, so add a field that will be written
$gun = $_GET['gun'];
$id = $_GET['id'];

$data = $db->sql( "
INSERT INTO attendance (member,gun) VALUES ($member,$gun)
" )->fetchAll();
echo json_encode($data);
exit();
}

<?php > ``` ?>

allan

I'm now struggling to insert data into this table. Please see same link..... error - request unknown parameter "firstname"

As in an SQL insert, or to display it in the table? It table appears to be shown fine, so I presume it is the SQL insert that is causing a problem?

If so, you need to follow the Editor client / server communication protocol. Specifically for the Edit command you would need to result an object with a row property that contains the data for the row that was created.

Allan

mbrennand

I'm trying to INSERT on action 'create' and then return data back to client. I'm confused with the link to the documentation you have sent.

allan

Which aspect of it are you confused above? The row object should be the data for the row in the DataTable - using the same JSON structure as was used to show the row in the DataTable.

Probably obvious, but you have an exit(); statement in your code above, so the insert command would never run.

Allan