multiple fields in side table when using left join

volnistii11

If we take an example from this link https://editor.datatables.net/examples/simple/join.html and imagine that there is one more field in the sites table - the date. Now our request will look like this:

SELECT users.first_name,users.last_name,users.phone, sites.name, sites.date
FROM users
LEFT JOIN sites ON users.site = sites.id`

how to change this in js and server script?

allan

Assuming you are using the PHP libraries like in that example, then you would just add sites.date to the fields list - e.g.:

Editor::inst( $db, 'users' )
    ->field(
        Field::inst( 'users.first_name' ),
        Field::inst( 'users.last_name' ),
        Field::inst( 'users.phone' ),
        Field::inst( 'users.site' )
            ->options( Options::inst()
                ->table( 'sites' )
                ->value( 'id' )
                ->label( 'name' )
            )
            ->validator( Validate::dbValues() ),
        Field::inst( 'sites.name' ),
        Field::inst( 'sites.date' )
    )
    ->leftJoin( 'sites', 'sites.id', '=', 'users.site' )
    ->process($_POST)
    ->json();

Likewise, on the client-side just add a column that uses columns.data pointing to the sites.date property that will now be in the JSON.

Allan

montoyam

for what it's worth, I like putting the joined fields after the join statement. it makes it more clear why you are even bringing in the second table, and if you need to test, you can comment out the join and the fields in one comment block

mvc .net version:

                var response = new Editor(db, "vw_CallManager_AsteriskData as Asterisk", "ExtensionNumber")
                    .Model<AsteriskMergeModel>("Asterisk")
                    .LeftJoin("vw_CallManagerDevices as Devices", "Devices.PKID", "=", "Asterisk.Device_PK")
                        .Field(new Field("Devices.DEVICE_NAME"))
                        .Field(new Field("Devices.CurrentDepartmentBilled"))
                    .LeftJoin("CallManager_Lines as Lines", "Asterisk.Line_PK", "=", "Lines.PKID")
                        .Field(new Field("Lines.DIRECTORY_NUMBER"))
                        .Field(new Field("Lines.VOICEMAILPROFILE"))
                    .Process(request)
                    .Data();
                return Json(response);

allan

Nice way of doing it - thanks for sharing!

Allan

volnistii11

ty, and in the editor's fields, how to write it?

allan

On the client-side? You typically would only have a single field in the Editor form which defined the value of the linked row (i.e. the primary key). If you need to edit the data in the linked table, consider using an approach like this.

Allan