3

A] Summary of the problem:

Using jquery datatable (http://www.datatables.net/) on the html page, Want to send data generated by query from python to javascript, so that it can be printed in the table. If someone can provide a sample implementation for this or a starter link, it would be awesome.

B] Models structure:

The hierarchical relationship between the models is as follows:

UserReportecCountry (one) to UserReportedCity(many)

UserReportedCity(one) to UserReportedStatus(many)

class UserReportedCountry(db.Model):
  country_name = db.StringProperty( required=True,
                          choices=['Afghanistan','Aring land Islands']
                         )

class UserReportedCity(db.Model):
  country = db.ReferenceProperty(UserReportedCountry, collection_name='cities')
  city_name = db.StringProperty(required=True)   

class UserReportedStatus(db.Model):
  city = db.ReferenceProperty(UserReportedCity, collection_name='statuses')
  status = db.BooleanProperty(required=True)
  date_time = db.DateTimeProperty(auto_now_add=True)

C] HTML code excerpt

The HTML code includes jquery , datatable javascript libraries. The datatable javascript library is configured to allow multicolumn sorting.

<!--importing javascript and css files -->
<style type="text/css">@import "/media/css/demo_table.css";</style>  
<script type="text/javascript" language="javascript" src="/media/js/jquery.js"></script>
<script type="text/javascript" src="/media/js/jquery.dataTables.js"></script>

<!-- Configuring the datatable javascript library to allow multicolumn sorting -->
<script type="text/javascript">
    /* Define two custom functions (asc and desc) for string sorting */
    jQuery.fn.dataTableExt.oSort['string-case-asc']  = function(x,y) {
        return ((x < y) ? -1 : ((x > y) ?  1 : 0));
    };

    jQuery.fn.dataTableExt.oSort['string-case-desc'] = function(x,y) {
        return ((x < y) ?  1 : ((x > y) ? -1 : 0));
    };

    $(document).ready(function() {
    /* Build the DataTable with third column using our custom sort functions */
    // #user_reported_data_table is the name of the table which is used to display the data reported by the users
    $('#user_reported_data_table').dataTable( {
        "aaSorting": [ [0,'asc'], [1,'asc'] ],
        "aoColumns": [
            null,
            null,
            { "sType": 'string-case' },
            null
        ]
    } );
} );
</script>

<!-- Table containing the data to be printed--> 
<div id="userReportedData">
<table cellpadding="0" cellspacing="0" border="0" class="display" id="user_reported_data_table">
    <thead>
        <tr>
            <th>Country</th>
            <th>City</th>
            <th>Status</th>
            <th>Reported at</th>
        </tr>
    </thead>

    <tbody>
        <tr class="gradeA">
            <td>United Status</td>
            <td>Boston</td>
            <td>Up</td>
            <td>5 minutes back</td>
        </tr>
    </tbody>
</table>

C] python code exceprt:

The code excerpt does the query of the data, puts the data in a "template" and send it to the HTML page ( this is off-course not working right now :( )

__TEMPLATE_ALL_DATA_FROM_DATABASE = 'all_data_from_database'
def get(self): 
  template_values = {
        self.__TEMPLATE_ALL_DATA_FROM_DATABASE: self.get_data_reported_by_users()
    }

    self.response.out.write(template.render(self.__MAIN_HTML_PAGE, template_values))

def get_data_reported_by_users(self):
    return db.GqlQuery("SELECT * FROM UserReportedCountry ORDER BY country_name ASC")

D] Technologies being used:

1] Jquery

2] Jquery datatable

3] Google app engine

4] Python

5] Django.

thank you for reading.

[EDIT#1]

Code based on the response given by @Mark

Tried the following

<!-- script snippet to setup the properties of the datatable(table which will contain site status    reported by the users) -->
<script type="text/javascript">
    /* Define two custom functions (asc and desc) for string sorting */
    jQuery.fn.dataTableExt.oSort['string-case-asc']  = function(x,y) {
        return ((x < y) ? -1 : ((x > y) ?  1 : 0));
    };

    jQuery.fn.dataTableExt.oSort['string-case-desc'] = function(x,y) {
        return ((x < y) ?  1 : ((x > y) ? -1 : 0));
    };

    $(document).ready(function() {
    /* Build the DataTable with third column using our custom sort functions */
    // #user_reported_data_table is the name of the table which is used to display the data reported by the users
    $('#user_reported_data_table').dataTable( {
        "aaSorting": [ [0,'asc'], [1,'asc'] ],
        "aoColumns": [
            null,
            null,
            { "sType": 'string-case' },
            null
        ],
        /* enabling serverside processing, specifying that the datasource for this will come from  
           file ajaxsource , function populate_world_wide_data
        */
        "bProcessing": true,
        "bServerSide": true,
        "sAjaxSource": "/ajaxsource/populate_world_wide_data"
    } );
} );
</script>

<div id="userReportedData">
<table cellpadding="0" cellspacing="0" border="0" class="display" id="user_reported_data_table">
    <thead>
        <tr>
            <th>Country</th>
            <th>City</th>
            <th>Status</th>
            <th>Reported at</th>
        </tr>
    </thead>
    <tbody>

    </tbody>
</table>

Python code, name of the file is ajaxsource.py

from django.utils import simplejson from google.appengine.ext import db

def populate_world_wide_data(self,request):
    my_data_object = db.GqlQuery("SELECT * FROM UserReportedCountry ORDER BY country_name ASC") 
    json_object = simplejson.dumps(my_data_object)        
    self.response.out.write( json_object, mimetype='application/javascript')

This however only showed "processing" on the table.

Couple of queries, How would the datatable know where to print the country, where to print the city and status?

[EDIT#2] Code based on the response given by @Abdul Kader

<script type="text/javascript" src="/media/js/jquery.dataTables.js"></script>

<!-- script snippet to setup the properties of the datatable(table which will contain site status reported by the users) -->
<script type="text/javascript">
    /* Define two custom functions (asc and desc) for string sorting */
    jQuery.fn.dataTableExt.oSort['string-case-asc']  = function(x,y) {
        return ((x < y) ? -1 : ((x > y) ?  1 : 0));
    };

    jQuery.fn.dataTableExt.oSort['string-case-desc'] = function(x,y) {
        return ((x < y) ?  1 : ((x > y) ? -1 : 0));
    };

    $(document).ready(function() {
    /* Build the DataTable with third column using our custom sort functions */
    // #user_reported_data_table is the name of the table which is used to display the data reported by the users
    $('#user_reported_data_table').dataTable( {
        "aaSorting": [ [0,'asc'], [1,'asc'] ],
        "aoColumns": [
            null,
            null,
            { "sType": 'string-case' },
            null
        ]
    } );
} );
</script>


<!-- Table containing the data to be printed--> 
<div id="userReportedData">
<table cellpadding="0" cellspacing="0" border="0" class="display" id="user_reported_data_table">
    <thead>
        <tr>
            <th>Country</th>
            <th>City</th>
            <th>Status</th>
            <th>Reported at</th>
        </tr>
    </thead>

   <tbody>
    <tr class="gradeA">
         {% for country in all_data_from_database %}
         <td>{{country}}</td>
         {%endfor%}
    </tr>
    </tbody>
</table>

Python code --

__TEMPLATE_ALL_DATA_FROM_DATABASE = 'all_data_from_database'

def get(self): 
    template_values = {
        self.__TEMPLATE_ALL_DATA_FROM_DATABASE: self.get_data_reported_by_users()
    }

    #rendering the html page and passing the template_values
    self.response.out.write(template.render(self.__MAIN_HTML_PAGE, template_values))

def get_data_reported_by_users(self):
    return db.GqlQuery("SELECT * FROM UserReportedCountry ORDER BY country_name ASC")

Item printed in the html page:

enter image description here

[EDIT#3] EDIT that has worked.

I modified the solution given by @Abdul Kader a bit and the following has worked

HTML code:

<!-- Table containing the data to be printed--> 
<div id="userReportedData">
<table cellpadding="0" cellspacing="0" border="0" class="display" id="user_reported_data_table">
    <thead>
        <tr>
            <th>Country</th>
            <th>City</th>
            <th>Status</th>
            <th>Reported at</th>
        </tr>
    </thead>

   <tbody>

    {% for country in countries %}
        {%for city in country.cities %}
            {%for status in city.statuses %}
                <tr class="gradeA">
                    <td>{{country.country_name}}</td>
                    <td>{{city.city_name}}</td>
                    <td>{{status.status}}</td>
                    <td>{{status.date_time }}</td>
                </tr>
            {%endfor%}  
        {%endfor%}      
    {%endfor%}

    </tbody>
</table>

Python code:

def get(self): 

   __TEMPLATE_ALL_DATA_FROM_DATABASE = 'countries'

    country_query = UserReportedCountry.all().order('country_name')
    country = country_query.fetch(10)

    template_values = {
        self.__TEMPLATE_ALL_DATA_FROM_DATABASE: country
    }

    self.response.out.write(template.render(self.__MAIN_HTML_PAGE, template_values))

Enhancement Request: I believe this is a very basic way to do this and there might be a solution that can involve a bit of ajax or more elegance. If someone has a example or a open source project that is using datatables based on python, please let me know.

Code review request: Can someone please review the code that i have done and let me know if i am doing a mistake or something that can be done better or in a more efficient manner.

Improve this question
3
  • @Nick Johnson, thank you for your response. I tried directly sending the data generated from the query to the html page, but that didnt work. Since this appears to be a common problem, i was hoping for a library that can transition the data, so that it can be read by the javascript code on the client side. I am sure writing my own custom library isnt the right solution and hence i posted the question. Commented Apr 11, 2011 at 1:56
  • 'Directly sending' how? In response to what? The datatable docs provide plenty of examples - basic usage is to just generate a regular HTML table. If you want to add data after page load, that's documented too, as @Mark demonstrates. Commented Apr 11, 2011 at 2:00
  • I think as a first step you should make sure that your table is rendered correctly. This seems to be the core of your problem. What about the row containing boston/usa ... is this just an example or the real output of your query? If you get your table rendered correctly, i would start with minimal options for datatables (datatables.net/examples/basic_init/zero_config.html) and add the more complex parameters later on. Commented Apr 11, 2011 at 3:11

2 Answers 2

Reset to default
2

You have to simply create the table from the datastore as you usually do. Plugin will take care of the rest. Models

class UserReportedCountry(db.Model):
  country_name = db.StringProperty( required=True,
                          choices=['Afghanistan','Aring land Islands']
                         )

class UserReportedCity(db.Model):
  country = db.ReferenceProperty(UserReportedCountry, collection_name='cities')
  city_name = db.StringProperty(required=True)   

class UserReportedStatus(db.Model):
  city = db.ReferenceProperty(UserReportedCity, collection_name='statuses')
  status = db.BooleanProperty(required=True)
  date_time = db.DateTimeProperty(auto_now_add=True)

Python

class MainPage(webapp.RequestHandler):
    def get(self):
        User_country=UserReportedCountry.all().fetch(1000)
        return self.response.out.write(template.render('#pathtohtml','{'user_c':User_country}))

HTML

<!--importing javascript and css files -->
<style type="text/css">@import "/media/css/demo_table.css";</style>  
<script type="text/javascript" language="javascript" src="/media/js/jquery.js"></script>
<script type="text/javascript" src="/media/js/jquery.dataTables.js"></script>

<!-- Configuring the datatable javascript library to allow multicolumn sorting -->
<script type="text/javascript">
    /* Define two custom functions (asc and desc) for string sorting */
    jQuery.fn.dataTableExt.oSort['string-case-asc']  = function(x,y) {
        return ((x < y) ? -1 : ((x > y) ?  1 : 0));
    };

    jQuery.fn.dataTableExt.oSort['string-case-desc'] = function(x,y) {
        return ((x < y) ?  1 : ((x > y) ? -1 : 0));
    };

    $(document).ready(function() {
    /* Build the DataTable with third column using our custom sort functions */
    // #user_reported_data_table is the name of the table which is used to display the data reported by the users
    $('#user_reported_data_table').dataTable( {
        "aaSorting": [ [0,'asc'], [1,'asc'] ],
        "aoColumns": [
            null,
            null,
            { "sType": 'string-case' },
            null
        ]
    } );
} );
</script>

<!-- Table containing the data to be printed--> 
<div id="userReportedData">
<table cellpadding="0" cellspacing="0" border="0" class="display" id="user_reported_data_table">
    <thead>
        <tr>
            <th>Country</th>
            <th>City</th>
            <th>Status</th>
            <th>Reported at</th>
        </tr>
    </thead>

    <tbody>
        <tr class="gradeA">
             {% for country in user_c %}
             <td>{{country}}</td>
             {%endfor%}
        </tr>
    </tbody>
</table>
Improve this answer
Sign up to request clarification or add additional context in comments.

6 Comments

@Abdul Kader, thank you for your response. Sorry for replying late, I am going to first try the response given by @Mark in the above comment and check if it works out. I will go thru your response as well and see if it is applicable to the scenario i am facing. Thanks again for your response.
@Abdul Kader, I tried the code based on your response. There were no problem with interpretation, but the code doesn't print any entries on the html datatable. The code and the resultant datatable are placed in the "EDIT#2" section of my main post.
@bhavesh make sure that your all_data_from_database fetches records. import logging and do some logging.info() to find if that actullay fetches record. Also make sure that the type of record already exists in datastore
@Abdul Kader, you made my night !!!. I modified the solution given by you slightly and "EDIT#3" has the solution that has worked. The problem was we were trying to print the "country" object, what was required was to access the "country_name" and other fields for the object. I still believe this is an in-efficient mechanism to do what i want to do. Can you please review my code and provide feedback based on your experience. Again thanks a lot man , your response was highly appreciated.
@bhavesh i don't think you need three for loops there. One for loop should be enough. Try to figure out how exactly templating system renders.
|
2

In the DataTables documentation, they show an example of returning data "server-side". In their example they are using PHP on the server, but how it's returned is by encoding with JSON. This is easily done with Python as well.

EDITS

The key is how the data is returned from the server:

$(document).ready(function() {
    $('#example').dataTable( {
        "bProcessing": true,
        "bServerSide": true,
        "sAjaxSource": "url/to/json/returning/python"
    } );
} );

In the javascript above, it would be calling the python Django view directly and expecting a JSON response.

The Django view would be something akin to (I am not a Django user, so this might be off):

from django.utils import simplejson

def ajax_example(request):
    ## do your processing
    ## your return data should be a python object
    return HttpResponse(simplejson.dumps(my_data_object), mimetype='application/javascript')
Improve this answer

6 Comments

i am having a hard time understanding the php code on your given link. I will spend some more time, but is there a easier way to do this ?
@bhavesh, if you can get around the PHP-ness of the example, it's actually pretty simple. See edits above.
sorry for the late response, i didn't have internet access for the last couple of days. Thank you for explaining on how this can be achieved, i am going to try doing this tonight ( ETA 8:00PM EST) and will provide results.
as promised, i tried the response you gave but couldnt get it working. I am sure i am making a very rookie mistake. The code i tried is placed in "[EDIT#1]" section of my main post.
I tried the mechanism provided by @Abdul Kader and have made progress in terms of getting the thing working. I believe the code i am using is inefficient and there might be a better mechanism to achieve what i want to do. Could you please do a code review of "EDIT#3" and let me know if i am making any mistakes. Again thanks Mark for reading my problem and providing a response.
|

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.