Displaying foreign key name in place of id asp.net mvc

slash_2184

I am showing the data of table tbl_assignment in datatable. It has a column named assignment_author_id which is a foreign key to the table tbl_author and the property is author_id. Now I want to show the author_name in place of the author id in my datatable.
My code for the tbl_assignment datatable-

rf1234

just use { "data": "author_name"} instead of "assignment_author_id". That of course requires a join of those tables on the server side assuming you use an SQL database.

As you can see here https://editor.datatables.net/examples/advanced/joinLinkTable.html

->leftJoin( 'sites',     'sites.id',          '=', 'users.site' )

the join is on sites.id but of course you want to display the name of the site (e.g. "London") in the table not its id.

rf1234

In plain SQL this looks like this:

...
SELECT assignment_title, assignment_details, author_name, assignment_category, assignment_create_date
FROM tbl_assignment
LEFT JOIN tbl_author ON  assignment_author_id = author_id
WHERE author_name IS NOT NULL

If you also have assignments that have no author and you also want to display those (with empty author name) remove the WHERE clause.

slash_2184

I am using the datatable in ASP.NET MVC project. That's why there is a big confusion.

allan

This is the documentation for Editor's .NET join implementation.

The key is the left join as @rf1234 says - you just need to use the .NET syntax for it shown in the documentation, rather than the PHP syntax.

Allan