[web2py] Re: Datatables Object ID

2019-04-13 Thread Anthony 
As described here , you can 
instead just do joins to include data from related tables. The resulting 
JSON will be an array of objects with nested records from each table. 
Datatables can handle such nested objects: 
https://editor.datatables.net/examples/advanced/deepObjects.html. The code 
is much simpler on the web2py side (even when joining multiple tables), and 
handling the nested objects in Datatables is very simple.

Anthony

On Saturday, April 13, 2019 at 4:23:09 PM UTC-4, Cristina Sig wrote:
>
> Thank you for the reply!
>
> I applied it and it works but I was thinking that this method is a little 
> bit tricky when it comes to tables that have more than one reference to 
> other tables.
> In my case, I have another table called Students which have references to 
> other two tables so in that case the code would look complex.
> Is there any other way to work with Datatables child row to show extra 
> information (reference information from other tables)?
>
>
> El sábado, 13 de abril de 2019, 12:46:03 (UTC-3), Massimo Di Pierro 
> escribió:
>>
>> Since you are using datatable you do not have mach choice and you have to 
>> handle this serverside. You want the json to contain the nationality 
>> description instead of the nationality id. This can be done in the 
>> controller.
>>
>> Before you do that two observation: User is a reserved keywork so I am 
>> going to use Person. Table Nationality should be defined before table 
>> Person to avoid trouble. So, given:
>>
>> db.define_table('Person',
>> Field('first_name', 'string'),
>> Field('last_name', 'string'),
>> Field('email','string'),
>> Field('username','string'),
>> Field('nationality','reference Nationality', requires = 
>> IS_IN_DB(db,db.Nationality.id,'%(description)s')
>>)
>>
>>
>> db.define_table('Nationality',
>> Field('description','string'),
>> format = '%(description)s'
>>)
>>
>> You can do:
>>
>> def user():
>> import json
>> rows = jdb(db.Person.id>0).select()
>> ids = [row.nationality for row in rows] # find the nationalities
>> mapping = db(db.Nationality.id.belongs(ids)).select().as_dict() # 
>> build a mapping in one extra query
>> for row in rows: row.nationality=mapping[row.nationality].description 
>> # applly the mapping
>> return dict(formListar=XML(rows.as_json()))
>>
>>
>>
>>
>> On Friday, 12 April 2019 20:58:36 UTC-7, Cristina Sig wrote:
>>
>>> Hello everybody,
>>>
>>> I have two tables and I am working with Datatables and what I would like 
>>> to do is use the child row to show more information about a particular row. 
>>> For example, on the table I will display first name, last name, username, 
>>> and email and on the child or expandable row, I will show the nationality 
>>> of that user.
>>> The problem I am having is that when I expand the row, it shows only the 
>>> id from the 'User' table  and not the description associated to that id.
>>> this is my first time dealing with Datatables so I don't have my 
>>> experience with it.
>>>
>>> Any suggestions to solve this issue?
>>>
>>> Thanks :)
>>>
>>> DB
>>> db.define_table('User',
>>> Field('first_name', 'string'),
>>> Field('last_name', 'string'),
>>> Field('email','string'),
>>> Field('username','string'),
>>> Field('nationality','reference Nationality', requires = 
>>> IS_IN_DB(db,db.Nationality.id,'%(description)s')
>>>)
>>>
>>>
>>> db.define_table('Nationality',
>>> Field('description','string'),
>>> format = '%(descripcion)s'
>>>)
>>>
>>>
>>>
>>> My controler
>>> def user():
>>> import json
>>> usuario = json.dumps(db(db.auth_user.id>0).select().as_list())
>>> return dict(formListar=XML(usuario))
>>>
>>>
>>>
>>>
>>> My view
>>> 
>>> var tabla;
>>> $(document).ready(function(){
>>>tabla=  $('#tablaGenerica').DataTable({
>>>  "data":  {{=formListar}},
>>> "scrollX": false,
>>>  "dom": 'lrtip',
>>>  "searching": true,
>>>  "sRowSelect": "single",
>>>   "columns": [
>>>   {
>>>  "class":"details-control",
>>>  "orderable":false,
>>>  "data":null,
>>>  "defaultContent": ""
>>>   },
>>>   { data: 'first_name' },
>>>   { data: 'last_name' },
>>>   { data: 'email' },
>>>   { data: 'username' },
>>>   ]
>>> });
>>>
>>>  $('#tablaGenerica tbody').on('click', 'td.details-control

[web2py] Re: Datatables Object ID

2019-04-13 Thread Cristina Sig 
Thank you for the reply!

I applied it and it works but I was thinking that this method is a little 
bit tricky when it comes to tables that have more than one reference to 
other tables.
In my case, I have another table called Students which have references to 
other two tables so in that case the code would look complex.
Is there any other way to work with Datatables child row to show extra 
information (reference information from other tables)?


El sábado, 13 de abril de 2019, 12:46:03 (UTC-3), Massimo Di Pierro 
escribió:
>
> Since you are using datatable you do not have mach choice and you have to 
> handle this serverside. You want the json to contain the nationality 
> description instead of the nationality id. This can be done in the 
> controller.
>
> Before you do that two observation: User is a reserved keywork so I am 
> going to use Person. Table Nationality should be defined before table 
> Person to avoid trouble. So, given:
>
> db.define_table('Person',
> Field('first_name', 'string'),
> Field('last_name', 'string'),
> Field('email','string'),
> Field('username','string'),
> Field('nationality','reference Nationality', requires = 
> IS_IN_DB(db,db.Nationality.id,'%(description)s')
>)
>
>
> db.define_table('Nationality',
> Field('description','string'),
> format = '%(description)s'
>)
>
> You can do:
>
> def user():
> import json
> rows = jdb(db.Person.id>0).select()
> ids = [row.nationality for row in rows] # find the nationalities
> mapping = db(db.Nationality.id.belongs(ids)).select().as_dict() # 
> build a mapping in one extra query
> for row in rows: row.nationality=mapping[row.nationality].description 
> # applly the mapping
> return dict(formListar=XML(rows.as_json()))
>
>
>
>
> On Friday, 12 April 2019 20:58:36 UTC-7, Cristina Sig wrote:
>
>> Hello everybody,
>>
>> I have two tables and I am working with Datatables and what I would like 
>> to do is use the child row to show more information about a particular row. 
>> For example, on the table I will display first name, last name, username, 
>> and email and on the child or expandable row, I will show the nationality 
>> of that user.
>> The problem I am having is that when I expand the row, it shows only the 
>> id from the 'User' table  and not the description associated to that id.
>> this is my first time dealing with Datatables so I don't have my 
>> experience with it.
>>
>> Any suggestions to solve this issue?
>>
>> Thanks :)
>>
>> DB
>> db.define_table('User',
>> Field('first_name', 'string'),
>> Field('last_name', 'string'),
>> Field('email','string'),
>> Field('username','string'),
>> Field('nationality','reference Nationality', requires = 
>> IS_IN_DB(db,db.Nationality.id,'%(description)s')
>>)
>>
>>
>> db.define_table('Nationality',
>> Field('description','string'),
>> format = '%(descripcion)s'
>>)
>>
>>
>>
>> My controler
>> def user():
>> import json
>> usuario = json.dumps(db(db.auth_user.id>0).select().as_list())
>> return dict(formListar=XML(usuario))
>>
>>
>>
>>
>> My view
>> 
>> var tabla;
>> $(document).ready(function(){
>>tabla=  $('#tablaGenerica').DataTable({
>>  "data":  {{=formListar}},
>> "scrollX": false,
>>  "dom": 'lrtip',
>>  "searching": true,
>>  "sRowSelect": "single",
>>   "columns": [
>>   {
>>  "class":"details-control",
>>  "orderable":false,
>>  "data":null,
>>  "defaultContent": ""
>>   },
>>   { data: 'first_name' },
>>   { data: 'last_name' },
>>   { data: 'email' },
>>   { data: 'username' },
>>   ]
>> });
>>
>>  $('#tablaGenerica tbody').on('click', 'td.details-control', function () 
>> {
>> var tr = $(this).closest('tr');
>> var row = tabla.row( tr );
>> if ( row.child.isShown() ) {
>> // This row is already open - close it
>> row.child.hide();
>> tr.removeClass('shown');
>> }
>> else {
>> // Open this row
>> row.child( format(row.data()) ).show();
>> tr.addClass('shown');
>> }
>> } );
>>
>> function format ( d ) {
>> // `d` is the original data object for the row
>> return '> style="padding-left:50px;">'+
>> ''+
>> '
Nationality:<




[web2py] Re: Datatables Object ID




2019-04-13

Thread
Massimo Di Pierro



Since you are using datatable you do not have mach choice and you have to 
handle this serverside. You want the json to contain the nationality 
description instead of the nationality id. This can be done in the 
controller.

Before you do that two observation: User is a reserved keywork so I am 
going to use Person. Table Nationality should be defined before table 
Person to avoid trouble. So, given:

db.define_table('Person',
Field('first_name', 'string'),
Field('last_name', 'string'),
Field('email','string'),
Field('username','string'),
Field('nationality','reference Nationality', requires = 
IS_IN_DB(db,db.Nationality.id,'%(description)s')
   )


db.define_table('Nationality',
Field('description','string'),
format = '%(description)s'
   )

You can do:

def user():
import json
rows = jdb(db.Person.id>0).select()
ids = [row.nationality for row in rows] # find the nationalities
mapping = db(db.Nationality.id.belongs(ids)).select().as_dict() # build 
a mapping in one extra query
for row in rows: row.nationality=mapping[row.nationality].description # 
applly the mapping
return dict(formListar=XML(rows.as_json()))




On Friday, 12 April 2019 20:58:36 UTC-7, Cristina Sig wrote:

> Hello everybody,
>
> I have two tables and I am working with Datatables and what I would like 
> to do is use the child row to show more information about a particular row. 
> For example, on the table I will display first name, last name, username, 
> and email and on the child or expandable row, I will show the nationality 
> of that user.
> The problem I am having is that when I expand the row, it shows only the 
> id from the 'User' table  and not the description associated to that id.
> this is my first time dealing with Datatables so I don't have my 
> experience with it.
>
> Any suggestions to solve this issue?
>
> Thanks :)
>
> DB
> db.define_table('User',
> Field('first_name', 'string'),
> Field('last_name', 'string'),
> Field('email','string'),
> Field('username','string'),
> Field('nationality','reference Nationality', requires = 
> IS_IN_DB(db,db.Nationality.id,'%(description)s')
>)
>
>
> db.define_table('Nationality',
> Field('description','string'),
> format = '%(descripcion)s'
>)
>
>
>
> My controler
> def user():
> import json
> usuario = json.dumps(db(db.auth_user.id>0).select().as_list())
> return dict(formListar=XML(usuario))
>
>
>
>
> My view
> 
> var tabla;
> $(document).ready(function(){
>tabla=  $('#tablaGenerica').DataTable({
>  "data":  {{=formListar}},
> "scrollX": false,
>  "dom": 'lrtip',
>  "searching": true,
>  "sRowSelect": "single",
>   "columns": [
>   {
>  "class":"details-control",
>  "orderable":false,
>  "data":null,
>  "defaultContent": ""
>   },
>   { data: 'first_name' },
>   { data: 'last_name' },
>   { data: 'email' },
>   { data: 'username' },
>   ]
> });
>
>  $('#tablaGenerica tbody').on('click', 'td.details-control', function () {
> var tr = $(this).closest('tr');
> var row = tabla.row( tr );
> if ( row.child.isShown() ) {
> // This row is already open - close it
> row.child.hide();
> tr.removeClass('shown');
> }
> else {
> // Open this row
> row.child( format(row.data()) ).show();
> tr.addClass('shown');
> }
> } );
>
> function format ( d ) {
> // `d` is the original data object for the row
> return ' style="padding-left:50px;">'+
> ''+
> ''+
> ''+
> ''+
> '
Nationality:'+d.nationality+'
';
> }
> 
>
>
>
>  cellspacing="0" width="100%" >
>   
>   
>  
>  First name
>  Last name
>  Email
>  Username
>   
>   
> 
>
>
>
>
>

-- 
Resources:
- http://web2py.com
- http://web2py.com/book (Documentation)
- http://github.com/web2py/web2py (Source code)
- https://code.google.com/p/web2py/issues/list (Report Issues)
--- 
You received this message because you are subscribed to the Google Groups 
"web2py-users" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to web2py+unsubscr...@googlegroups.com.
For more options, v






[web2py] Re: Datatables Object ID




2019-04-13

Thread
Anthony



See https://stackoverflow.com/a/55667115/440323.

On Friday, April 12, 2019 at 11:58:36 PM UTC-4, Cristina Sig wrote:
>
> Hello everybody,
>
> I have two tables and I am working with Datatables and what I would like 
> to do is use the child row to show more information about a particular row. 
> For example, on the table I will display first name, last name, username, 
> and email and on the child or expandable row, I will show the nationality 
> of that user.
> The problem I am having is that when I expand the row, it shows only the 
> id from the 'User' table  and not the description associated to that id.
> this is my first time dealing with Datatables so I don't have my 
> experience with it.
>
> Any suggestions to solve this issue?
>
> Thanks :)
>
> DB
> db.define_table('User',
> Field('first_name', 'string'),
> Field('last_name', 'string'),
> Field('email','string'),
> Field('username','string'),
> Field('nationality','reference Nationality', requires = 
> IS_IN_DB(db,db.Nationality.id,'%(description)s')
>)
>
>
> db.define_table('Nationality',
> Field('description','string'),
> format = '%(descripcion)s'
>)
>
>
>
> My controler
> def user():
> import json
> usuario = json.dumps(db(db.auth_user.id>0).select().as_list())
> return dict(formListar=XML(usuario))
>
>
>
>
> My view
> 
> var tabla;
> $(document).ready(function(){
>tabla=  $('#tablaGenerica').DataTable({
>  "data":  {{=formListar}},
> "scrollX": false,
>  "dom": 'lrtip',
>  "searching": true,
>  "sRowSelect": "single",
>   "columns": [
>   {
>  "class":"details-control",
>  "orderable":false,
>  "data":null,
>  "defaultContent": ""
>   },
>   { data: 'first_name' },
>   { data: 'last_name' },
>   { data: 'email' },
>   { data: 'username' },
>   ]
> });
>
>  $('#tablaGenerica tbody').on('click', 'td.details-control', function () {
> var tr = $(this).closest('tr');
> var row = tabla.row( tr );
> if ( row.child.isShown() ) {
> // This row is already open - close it
> row.child.hide();
> tr.removeClass('shown');
> }
> else {
> // Open this row
> row.child( format(row.data()) ).show();
> tr.addClass('shown');
> }
> } );
>
> function format ( d ) {
> // `d` is the original data object for the row
> return ' style="padding-left:50px;">'+
> ''+
> ''+
> ''+
> ''+
> '
Nationality:'+d.nationality+'
';
> }
> 
>
>
>
>  cellspacing="0" width="100%" >
>   
>   
>  
>  First name
>  Last name
>  Email
>  Username
>   
>   
> 
>
>
>
>
>

-- 
Resources:
- http://web2py.com
- http://web2py.com/book (Documentation)
- http://github.com/web2py/web2py (Source code)
- https://code.google.com/p/web2py/issues/list (Report Issues)
--- 
You received this message because you are subscribed to the Google Groups 
"web2py-users" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to web2py+unsubscr...@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.