querying the database from view web2py

Question

I am trying to query a database table from the view in web2py as I need to take a field from another table for each row in the present table so I have written a code like this:

{{for recipe in rows:}}

<div class="well">
    <table>
        <tr>
            <td>
             <div style="text-align:center">
<img width="200px"
     src="{{=URL('download', args=db(db.uploads.recipe_id==recipe.id).select().first().up_file)}}" />
</div>

            </td>
            <td><button>
            -
            </button></td><td><span class='votes'>{{=recipe.votes}}</span></td><td><button>
            +
            </button><td><strong>{{=A("comments",_href=URL('view_posts',args=recipe.id))}},{{=recipe.name}}</strong></td></td></tr>
    </table>

</div>
{{pass}}

but I have a doubt whether we could query a database from a view or not? If not how could I query the same from the controller and return it to the view? This may be a silly doubt,but sorry I am new to web2py

score 1 · Answer 1 · answered Mar 21 '16 at 17:26

You can do that, but it is not very efficient, as you will have a separate query for each row in the table. Instead, your query to create the rows object in the controller should involve a join with the db.uploads table:

    rows = db((your_current_query) & (db.uploads.recipe == db.recipe.id)).select()

Then in the view:

{{for row in rows:}}

<div class="well">
    <table>
        <tr>
            <td>
             <div style="text-align:center">
<img width="200px"
     src="{{=URL('download', args=row.uploads.up_file)}}" />
</div>

            </td>
            <td><button>
            -
            </button></td><td><span class='votes'>{{=row.recipe.votes}}</span></td><td><button>
            +
            </button><td><strong>{{=A("comments",_href=URL('view_posts',args=row.recipe.id))}},{{=row.recipe.name}}</strong></td></td></tr>
    </table>

</div>
{{pass}}

Note, because the rows object now represents a join between two tables, you must use both the table name and the field name to access a given value (e.g., row.recipe.name rather than row.name). To make that clear, in the for loop, I changed recipe to row.

querying the database from view web2py

1 Answers1