This page collects reusable tal examples.

Tal to filter database results using filter_sql

The following creates a select box by querying the database looking for users with particular roles. It use filter_sql and is an example of how to access the database from tal.

Example taken from:

• http://hg.python.org/tracker/roundup/file/2a6874eea8c5/website/issues/html/issue.item.html

after a discussion on #roundup complaining about how:

• http://roundup.sourceforge.net/docs/customizing.html#restricting-the-list-of-users-that-are-assignable-to-a-task

fails to scale if you have a lot of users as it iterates over all users.

Note IIUC most fields in the database are prefixed with _ to allow arbitrary naming of fields even if they might be reserved words in sql. So the "username" property can be retrieved by selecting "_username".

 <td tal:condition="context/status/is_edit_ok">
  <select name="assignee">
   <option value="-1">nobody</option>
   <tal:block tal:repeat="userdata python:db._db.user.filter_sql('select id,_username from _user where _roles like \'%Developer%\' order by _username')">
    <option tal:attributes="value python:userdata[0];
                            selected python:str(userdata[0]) == context.assignee._value"
            tal:content="python:userdata[1]"></option>
   </tal:block>
  </select>
 </td>