Datatables Editor - Loading Select Options from database in a Django App
Datatables Editor - Loading Select Options from database in a Django App
keila_ortiz
Posts: 10Questions: 3Answers: 0
I have a Django app and I'm using Datatables to display the information(json is received with url:'all_json'). That part works great, but now I'm working with the editing part. The first thing I want to do, which I'm having problems with, is to load "select options" straight from the database. I have more than one field that needs to be populated from the database, so I'm guessing that I have to make more than one ajax call. 'all_json has all the entries in the main table in the database, but it does not include all the options in the category and specialty tables. I need to display all the options. Right now I'm trying ti display the categories. Below is the latest code:
html
...
<body>
<div id='container'>
<table id="results_table" class="formdata" summary="Schedule">
<thead>
<tr>
<th></th>
<th scope="col">Scheduled</th>
<th scope="col">Venue</th>
<th scope="col">Category</th>
<th scope="col">Specialty</th>
</tr>
</thead>
</table>
</div>`
<script>
var editor;
$(document).ready(function() {
editor = new $.fn.dataTable.Editor({
ajax: 'all_json',
table: "#results_table",
fields: [{
label: "Category",
name : "Venue.category",
type: "select",
placeholder: "Category"
},
{ label: "City",
name : "Venue.city.sector",
type: "select",
placeholder: "City"
}]
});
// Activate an inline edit on click of a table cell
$('#results_table').on( 'click', 'tbody td:not(:first-child)', function (e) {
editor.inline( this );
});
var table = $('#results_table').DataTable( {
dom: "Bfrtip",
"processing": true,
"ajax": {
"processing": true,
"url": 'all_json',
"dataSrc": "",
},
"columns": [
{className: 'details-control',
orderable: false,
data: null,
defaultContent: ''
},
{ "data": "Venue.city.sector"},
{ "data": Scheduled.category.title", editField: "Venue.category"},
],
keys: {
columns: ':not(:first-child)',
editor : editor
},
select: {
style : 'os',
selector : 'td:first-child'
},
buttons: [
{ extend: "create", editor: editor },
{ extend: "edit", editor: editor },
{ extend: "remove", editor: editor }
]
});
});
</script>
I tried using this example but the select option does not populate and it would only include the categories and cities I already have scheduled and not include the other options on the tables.
https://datatables.net/reference/option/columns.editField
This would work if I had a small list, but the list of categories and cities are quite long: [simple select option]https://editor.datatables.net/examples/simple/fieldTypes.html
How can I populate the city and category fields with information from the database? Can I have multiple calls like all_json?
Answers
The normal route for this is to include the select information for the fields in the Ajax data read by DataTables (Editor will listen for that event and check to see if there is any information for it to populate its fields with).
However, since you are using
ajax.dataSrcas an empty string to indicate that you are only loading an array of data, you can't take that approach (since you would need to use an object, allowing a split between the DataTables row data and the Editor options).If you can change that, I would. However, if that isn't an option then make a single Ajax call to get all of the options for the fields and use the
field().update()method for eachselectfield in turn to populate its options.Allan
Allan,
Thank you for your reply. The reason I'm using dataSrc as an empty spring is because I'm following this post: https://dev.to/codeshard/datatables-and-django-finally-with-ajax
In all honesty I'm new to this and don't know what dataSrc does.
Also, I don't understand where I would use field()update. Do I have to pass another queryset to AJAX?
I can't even make this work:
It works on the reg Datatable but not with the dataTable.Editor
dataSrc: have a look at this example. It loads data with the structure:Then compare to this example which uses this JSON structure:
If you can, I would suggest using the latter (I can't help with the Django aspect of that I'm afraid, I've never written anything in Django!).
With the latter you can then use the options parameter to have Editor automatically populate the select lists. There is an example of that here.
Allan
Ok. I deleted ""dataSrc": "", but now
{ "data": "Venue.city.sector"},
{ "data": Scheduled.category.title"}
do not work. The data is not populating the table.
And when I use dataSrc: "" that part works but not for the select options.
Did you also update the JSON data returned by the server to use an object that has a
dataproperty rather than the plain array?Regards,
Allan
How do I do that?
It will be a modification required in your Django app as I mentioned above. I'm afraid I really don't know Django at all so I can't help with the server-side aspect.
Looking at the article you linked to it uses:
So you'd need to wrap up
object_listinside another object to give this structure:I realise that isn't the response you want and I'll refund the support payment if you like, but I really don't know Django! That said, if you can make that modification on the server-side I can still help with the client-side.
Allan
Try:
Kevin
Awesome - thanks Kevin.
Once you've got that, you need to have an
optionsobject which I guess will be something like:where the
...would be replaced by the options object such as (from this example):One property inside the object for each options list you want to fill. The reference documentation for that is available here.
Allan
Well, the thing is that when I serialized it like you specified, I only get the information from one table. Whereas, before I was getting a full queryset that contained all the information.
I serialized it using django rest framework:
That gave me the full nested queryset that I could use with dataSrc "". And I could access the values like this:
{ "data": "Venue.city.sector"},
{ "data": Scheduled.category.title"
I know that you mentioned that I can [joint tables], but I don't know how to send all those tables and the relationships from Django to the template other than the method I just typed above. And with that it only includes the information from the main table(it didn't include all the category options") only the ones that are currently been used. I can, however, serialize that table and send it to the template with another method(lets call it 'category_json'). What I need to do is get that into the select option menu.
(https://editor.datatables.net/examples/simple/join.html "joint tables")
Is it possible to just use the dataSrc " " and load the options some other way?
Yes. You can use:
That seems odd - does
serializednot contain the data from both tables (i.e. the fully joined data)?Allan
I'm not sure what the serializer does in Django. I assumed it did the function that
json.dumps(serialized)is doing. Before you use json.dumps you should place your table data into a dictionary with the keydata. Lets say your tale data is in the dictionaryobject_listand looks similar to this structure:You will need to combine the table data with your options into a new dictionary with the key
datacontaining the table data. The dictionary should look similar to this:Then use json.dumps to serialize this dictionary into a json string. Remove the '"dataSrc": ""
so Datatables will look for the table data in thedata` object.HTH,
Kevin