multiple fields in side table when using left join

multiple fields in side table when using left join

volnistii11volnistii11 Posts: 49Questions: 16Answers: 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?

This question has an accepted answers - jump to answer

Answers

  • allanallan Posts: 54,881Questions: 1Answers: 8,601 Site admin
    Answer ✓

    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: 517Questions: 125Answers: 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);
    
  • allanallan Posts: 54,881Questions: 1Answers: 8,601 Site admin

    Nice way of doing it - thanks for sharing!

    Allan

  • volnistii11volnistii11 Posts: 49Questions: 16Answers: 0

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

  • allanallan Posts: 54,881Questions: 1Answers: 8,601 Site admin

    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

Sign In or Register to comment.