multiple fields in side table when using left join

multiple fields in side table when using left join

volnistii11volnistii11 Posts: 47Questions: 15Answers: 0

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?

Answers

  • allanallan Posts: 54,680Questions: 1Answers: 8,552 Site admin

    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

  • montoyammontoyam Posts: 495Questions: 117Answers: 5

    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);
    
Sign In or Register to comment.