Web2py databases: How do I select a subset of rows from selected rows? And how do I pass it to JavaScript?

I have a table and want the web2py action to pass all its contents for the view. The view then selects a subset from it and manifests them one at a time in iteration.

Here's a sample table in db.py:

db.define_table('block',
    Field('location'),
    Field('propertyA'),
    Field('propertyB')
)

A sample action in controller default.py:

def demo():
    return dict(blocks=db(db.block).select())

So far so good. It compiles, doesn't crash and after running a few tests it did what I wanted.

But now for the view. In this example, I would want to select all whose "propertyA" is, say, 5. And then I want to run a loop, which prints them in to a table, which already exists. The table has 100 cells and id's are 1-100. I want to print the values of propertyB to the table cells, whose id matches the blocks' location.

A sample view default/demo.html:

{{extend 'layout.html'}}

<style>
    table#map, td {
        border: 1px solid black;
        border-collapse: collapse;
    }
    td {
        background-color: gray;
        width: 50px;
        height:50px;
        text-align: center;
        color: white;
    }
</style>

<!--This creates a 10*10 table with running id from 1 to 100-->
<table id="map">
    <caption>Map</caption>
    {{for y in range(10):}}
    <tr>
        {{for x in range(10):}}
        {{=TD('', _id=10*y+x)}}
        {{pass}}
    </tr>
    {{pass}}
</table>

<!--then I want to select a subset from blocks, whose propertyA is 5
These lines crash if uncommented.-->
{{#query = (propertyA == 5)}}
{{#subset = blocks(query).select()}}

<!--and run a loop which, which iterates the subset, and in each
iteration, writes the value of propertyB, if cell's id and block's location
match. I just made a place holder function, because I don't know how to
pass python variables/objects to javascript-->
<script>
    //var subset = "subset from python";
    function myFunction() {
        var i;
        for (i = 0; i < 100; i++) {
            //var cell = document.getElementById(i);
            //if(subset(location===cell.id).select() === True) {
                //var value = subset(location===cell.id).propertyB;
                //cell.innerHTML = value;
            //} else {
                //cell.innerHTML = '';
            //}
        }
    }
</script>

So I have no idea how this should be done. And the web2py tutorial book is quite stingy on info about this. Or am I having a completely wrong approach to this? Because I think it could also be done with ajax calls, but I don't feel like making a database server query 100 times in a row is the right thing to do.

.select is a method of a DAL Set object and returns a Rows object -- you cannot then apply the .select method again to the Rows object. Instead, the Rows object has a .find method, which returns a new filtered Rows object:

blocks.find(lambda row: row.propertyA == 5)

If you want to use the subset of propertyA values in Javascript, you will need to write it into Javascript code in the template. First, you will have to extract the individual values from each row into a list, and then convert that to JSON in order to use that as a Javascript variable:

{{from gluon.serializers import json}}

<script>
  var subset = {{=json([r.propertyA for r in blocks if r.propertyA == 5])}}
</script>

Here we simply use the template delimiters ({{ }}) to write the results of the Python code directly into the Javascript code in order to define the value of the subset variable.

Also, notice that because a list comprehension is used to generate the list of propertyA values, it is possible to use the if clause to filter the records rather than using the .find method.

It is generally best to minimize the amount of Python logic in the views (harder to read, debug, and test), so it might be better to create all of the JSON arrays in the controller and simply pass them to the view.

Also, if the number of records in blocks is large, filtering in Python might be slow, so it could end up being faster to do separate database queries for each subset needed, rather than doing a single query and building subsets from that purely in Python (especially if each subset requires different fields from the database table -- that way you can limit each query to only the fields required, which will further improve performance). Maybe not worth worrying about right now, but if performance becomes an issue, you can do some profiling to determine the best approach.