Toscawidgets 2 and Turbogears 2.2

[Craig Small]

Hi,
There are muliple tutorials out there showing how toscawidgets works
with turbogears but often they are contradicting in how they come about
it. A good example is some use the middlewear, some use a specific
call (eg a data routine) and then others bring up the data in the
actual function that generates the grid.

I'm specifically interesting in the jqgrid setup where the grid is
displayed once and it then makes calls to get specific data using, i
guess a json transfer happens there. Also I've not seen how the query
does sensible limiting either; what i mean is if you have several
thousand records you dont want to load them all in and then ditch
most to display the middle 10. I couldn't see how that was done.

I've 2.2.0rc2 here on my projects and they seem to be going ok.

- Craig
--
Craig Small VK2XLZ http://enc.com.au/ csmall at : enc.com.au
Debian GNU/Linux http://www.debian.org/ csmall at : debian.org
GPG fingerprint: 5D2F B320 B825 D939 04D2 0519 3938 F96B DF50 FEA5

[Michael Pedersen]

Hello, Craig.

I'm not sure what question you're actually asking here, but I'll try to explain what I can.

The tutorials you've read are not actually contradictory in any way. Each of those pieces plays a role in getting the data to the user.

The TW middleware is used to insert <script> tags, CSS links, and other related pieces, when the HTML for a page is being generated.

The controller method that is called is responsible for generating the specific data that will be loaded into the user's web browser. Frequently, this will be JSON, and have some sort of limitation in place to prevent sending thousands of records.

Then there is the actual Javascript running in the browser. It makes the request to get the JSON data, and then does something with the data once it gets there.

So, the pieces are complementary, not contradictory. I hope this helps. If it doesn't, please ask, and I'll try to answer. I'm sorry I didn't answer last night, but we had a power failure here. Yes, it sucked.

--
You received this message because you are subscribed to the Google Groups "TurboGears" group.
To post to this group, send email to turbo...@googlegroups.com.
To unsubscribe from this group, send email to turbogears+...@googlegroups.com.
For more options, visit this group at http://groups.google.com/group/turbogears?hl=en.

--
Michael J. Pedersen

My Online Resume: http://www.icelus.org/ -- Google+ http://plus.ly/pedersen

Google Talk: m.ped...@icelus.org -- Twitter: pedersentg

[Rob]

Hi Craig,

With regard to the tw2.jqplugins.jqgrid specifically, there are two parameters 

passed to `options` which need to be controlled:  'url' & 'editurl', the former

for the json data request, the latter being used to control add/edit & delete

events fired by the respective buttons on the the jqgrid's nav bar.

To start with the former, options= {'url': '/data_request/'} requires a 

corresponding controller, something along the lines of: 

@expose('json')

@validate(validators=[some_validator])

def data_request(self, page=1, rows=30, sidx=1, sord='asc', _search='false',

                    searchOper=u'', searchField=u'', searchString=u'', **kw):

                    

    qry = DBSession.query()

    qry.filter()         # based on searchField

    

    qry = qry.order_by() # based on sidx/sord

    offset = (page-1) * rows

    qry = qry.offset(offset).limit(rows)

 

    records = [{'id': rw.id),

                'cell': [ rw.x, rw.y , rw.z ]} for rw in qry]

 

    total = int(ceil(result_count / float(rows)))

 

    return dict(page=page, total=total, records=result_count, rows=records)

 

 

The limit/offset (as specified by the `page` & `rows` means that the 'thousands 

of rows' you mentioned aren't returned. 

 

(note: using a formencode validation schema in the @validate is very useful as 

this will deal with string to python-type conversion for you before the 

controller's hit.)

Add/edit/delete functionality is dealt with by options = {'editurl': '/myediturl/'}

And would require a controller that looks something like...

@expose('json')

def myediturl(self, oper, **kw):

    if oper == 'add':

        ... do stuff ...

    elif oper == 'edit':

        ... do stuff ...

    elif oper == 'del':

        ... do stuff ...

        

    return dict() 

        

        

Hope that helps!

Rob

[Rob]

PS.  Just spotted;   the variable I've called result_count needs to be computed

before the limit/offset -- ie it's the total number of rows in the recordset.

Am Mittwoch, 18. Juli 2012 23:01:12 UTC+1 schrieb Craig Small:

[Craig Small]

On Thu, Jul 19, 2012 at 09:04:33PM -0400, Michael Pedersen wrote:
> I'm not sure what question you're actually asking here, but I'll try to
> explain what I can.

OK, I'll give some examples. It could well be I'm just confused, but it
probably means others are too. TG (and TW) are both great, it is just a
bit hard to understand the documents.

http://turbogears.org/2.1/docs/main/ToscaWidgets/Cookbook/FlexiGrid.html
This is the style Rob also mentions. This tutorial uses tw1 not tw2 but
it seems to follow through. The FlexiGrid uses a json call back to the
server called fetch. I'm a little confused why you would do a query
THEN place a limit about 5 lines down, doesn't this mean you fetch 100s
of database rows and then throw away all but 25 of them?
This documentation is deprecated in 2.2 with no replacement, I assume
because it is TW1 and not TW2.
Both the widget and the json method have the model in them. The widget
specifies what columns are seen and the method access the specific
model. This way seems the clearest to me.

http://www.toscawidgets.org/documentation/tw2.core/turbogears.html
About 3/4 down the page there is a section called "Getting Fancier"
This grid accesses its data through a url like
/tw2_controllers/db_jqgrid/ but to get it to work you have to muck
around with the app_config.py to make a new middleware. It only
talks about TG2.1 and 2.0 Uses the SQLAjqGridWidget which filters the
column and what model to query.

http://threebean.org/blog/2011/04/30/tutorial-melting-your-face-off-with-tw2-and-turbogears2-1/
Ralph Bean's tutorial. This had a lot of those "I get it now" moments.
This creates a widget inherited from SQLAjqGridWidget that then calls
a common url. But the method is two lines long. The widget specifies
the model used.

For #2 and #3 I don't see how you can do things like filter from the
initial standpoint. The filters work with a user playnig with a grid but
for a page like /parent/42 I'd like to display a grid of all the children
of parent #42 and only show them no matter what the user clicks on the
grid.

They all work btw, I have test code that more or less does all three
methods; I'm just not sure what is the right one to use.

[Rob]

Hi Craig,

My turn to be a little confused....(!) 
Simplest things first (and my very sincere apologies in advance if I tell you anything you already know!!):

1. Flexigrid / jqgrid -- two different libraries (functionally and feature-wise, the latter is the more powerful)
   
2. TW / TW2 -- two rather different 'engines'
3. core.jqGridWidget -- perhaps think of this as access to the raw jqgrid and a means of displaying rows of 'data'
4. sqla.SQLAjqGridWidget -- a jqgrid which is closely bound to an sqlalchemy object model.

As you say, they all work but they (perhaps) serve different purposes.  As to flexigrid/jqgrid, your choice... but the latter is unquestionably more advanced and I find it aesthetically more pleasing (as far as I'm aware there's no TW2 widget/wrapper for flexigrid).

As to TW / TW2 (and in fact as per the tutorial you linked to by Ralph Bean), in a new project I simply switch TW off and use only the latter. As per the docs etc. TW and TW2 can be used in the same project (ie they can both be 'switched on' at the same time) but I prefer the syntax of the latter and -- here I really hope that I'm not speaking out of line(!) -- consensus appears to be in favour of TW2, that's certainly where the development is most active.

Just very quickly, I'll address the question you asked about having to query and "then throw away all but 25 of them".   If you look at the resulting jqgrid you'll see in the pager/navgrid "page 1 of P" and on the right hand side, "records 1 to n of Z",  where Z is the total number of rows in the recordset.  So, very simply let's say there are 10,000 records in your table, you've now filtered the recordset to show only those whose first name is "Mary", and you're left with 300 records. If you look carefully at the code I sent, this will now become immediately apparent   You query/filter --- and then count the total number records (the Z has to come from somewhere, right? :)  ) and then limit/offset depending on the number of records to display per page (let's say 25) and the page number you're on.  (that make sense?!)

So, time to close some doors!  We're now talking about using jqgrid with TW2:  your choice of jqGridWidget vs SQLAjqGridWidget...  And now it very much depends on what it is that you're trying to accomplish and the 'object model' you're using.  Fundamentally, it's a question about 'layers of abstraction':

• the SQLAlchemy ORM sits on top of the 'expression language', 
  
• SQLAjqGridWidget sits on top of (indeed, inherits from) jqGridWidget.
• (the jqgrid itself just wants to point at a URL which will give it a json representation of the data)

Very broadly speaking, if you have a simple object model and, all things being equal, in order to query it you'd use the object model/ORM then the SQLAjqGridWidget will almost certainly get you there...  Essentially, the SQLAjqGridWidget takes the job away from you and constructs the [@expose('json')] controller method/query for you -- granted, you lose visibility of what exactly is being queried but in a great many cases this may well be exactly what you're looking for.

If on the other hand the object model is more complex or you find yourself needing (or wanting) to use the expression language directly (don't forget that the ORM is an abstraction of the expression language, it's slower and provides less fine-grained control) then you will almost certainly have to provide the controller method (ie the code snippet that I provided earlier) yourself. In a nutshell, if you want full control of the query you'll be using jqGridWidget.

I really hope that this explanation has revealed more than it's obscured, but if you still have questions I'd be happy to try and help.

Regards,

Rob

[Craig Small]

On Sat, Jul 21, 2012 at 07:42:16AM -0700, Rob wrote:
> Hi Craig,
>
> My turn to be a little confused....(!)
> Simplest things first (and my very sincere apologies in advance if I tell
> you anything you already know!!):

> 1. Flexigrid / jqgrid -- two different libraries (functionally and

> feature-wise, the latter is the more powerful)

> 2. TW / TW2 -- two rather different 'engines'
> 3. core.jqGridWidget -- perhaps think of this as access to the raw jqgrid

> and a means of displaying rows of 'data'

> 4. sqla.SQLAjqGridWidget -- a jqgrid which is closely bound to an

> * the SQLAlchemy ORM sits on top of the 'expression language',
> * SQLAjqGridWidget sits on top of (indeed, inherits from) jqGridWidget.
> * (the jqgrid itself just wants to point at a URL which will give it a

> json representation of the data)
>
> Very broadly speaking, if you have a simple object model and, all things
> being equal, in order to query it you'd use the object model/ORM then the
> SQLAjqGridWidget will almost certainly get you there... Essentially, the
> SQLAjqGridWidget takes the job away from you and constructs the
> [@expose('json')] controller method/query for you -- granted, you lose
> visibility of what exactly is being queried but in a great many cases this
> may well be exactly what you're looking for.
>
> If on the other hand the object model is more complex or you find yourself
> needing (or wanting) to use the expression language directly (don't forget
> that the ORM is an abstraction of the expression language, it's slower and
> provides less fine-grained control) then you will almost certainly have to
> provide the controller method (ie the code snippet that I provided
> earlier) yourself. In a nutshell, if you want full control of the query
> you'll be using jqGridWidget.
>
> I really hope that this explanation has revealed more than it's obscured,
> but if you still have questions I'd be happy to try and help.

It sure did clear things up Rob, thanks as its helped and I've got a
clear path ahead now. A lot of my queries are not simple ones so that
is sounding like your first suggestion is the way to go.

This is probably a sqlalchemy question, but where does the SQLa hit the
database? You see on line 4 you do your query, but line 8 you have limit
and offset, between those lines its not pulling the entire set of rows
in and then in line 8 throwing it away. I'm from the php mysql function
days so is it just a matter of 'sqlalchemy does the right thing
normally, dont worry about it'?

[Rob]

Stepping through the code/trying this from the prompt would be instructive, but yes, the database would get hit twice. 

The first time (with something like result_count = qry.count())  hence, using a native function which should be pretty well optimised and sqlalchemy should 'do the right thing'.

And the second time, post the limit/offset, with the `for rw in qry`  (Think of the bit in-between as refining a sql string to be emitted)

So whilst yes, the database would get hit twice, the important thing to note is that what's transported back to the browser is an int for the total number of rows and then the subset (say, 25/30) rows. 

Glad that helped!

Rob 

[Craig Small]

A little while ago I asked about the jqrid in turbogears. I have now got
it serving basic data after following some strict guidelines.

The next thing is filtering. If I type something into one of the columns
I can see the words being passed backed to the json-sending function but
unsure how to parse it.

**kw of the json function gets the following:
'filters': u'{"groupOp":"AND","rules":[{"field":"Type","op":"bw","data":"GGG"}]}'}

ok so its a filter, looking for "GGG" in the column "Type".
How do I make that into something sqlalchemy understands?
filter(model.Blah.Type == "GGG")

Also is there a way to "pre-filter" a grid? Imagine a parent/child model
and on the parent screen i want to show a grid with only its children.
I could fudge it with postData : { 'Parent': 123 } in the grid options
and then make the json function look for it but is there a better way?