Working with the database

DB interface reference

Tabbli provides a global DB interface for access to the database. It is available inside all sites templates, automation scenarios and computed properties templates. You can use the next optional argument with it:

• request - use it to specify current request object, it is using by filtering component

If you create the object like DB(request) you will be able to use a list of methods:

• get_section(key, **options) - returns a SectionInterface object as an interface for a section with specified key. You can specify extra arguments for passing different options to the interface. In most cases you do not need to use this method.

• get_collection(key,**options) - returns a CollectionInterface object as an inteface for a collection with specified key. All collections keys are unique, so you do not actually use get_section as part of the chain, use get_collection directly.

• get_variable(name, default_value=None) - returns a value of system variable with specified name. Returns default_value if the variable does not exist.

• set_variable(name,value) - set system variable with specified name to value. If the variable does not exist it will be created.

• inc_variable(name, delta=1, start_value=0) - increment a variable with specified name for delta (1 by default). If the variable does not exist it will be created with a value start_value+delta. The variable must contain only integer value. In other case an error will be generated.

• dec_variable(name, delta=1, start_value=0) - works like inc_variable but for decrement.

SectionInterface supports the next methods:

• get_collection(key,**options) - returns a CollectionInterface object as an inteface for a collection with specified key. Work the same as get_collection method from DB.

• get_object() - returns an internal Section object. You can use it for some advanced use cases.

CollectionInterface supports the next options:

• records_per_page (default value is None) - activates using pagination and limits a result for specified records count per page. Set it to integer value for enabling pagination. Or you can use None for disabling it (returns all records as a single page). Do not forget to use pagination if you result could contain more than 30 records (approximately). Tabbli also has internal limit for getting records on a single page which is not strict and calculates individually for different data sets.

• page (default value is 1) - specify active page for paginator. Should be integer and more than 1.

• use_page_number_param (default value is None) - using for passing GET parameter value with specified name from current page URL for override page parameter. For example, if you URL looks like /results/?page=1 you can set use_page_number_param='page' for automatically init page option with number 1. It is a shortcut. In other case you need to use construction like page=request.GET.get('page')|int(1) for doing the same. Also if use use_page_number_param option system will do some basic error resolving (like not valid value for example).

• subset_key - specify a subset record key for filter results.

• parent_key - specify a record key if you want to query children records for some parent record.

• request - the same option like in DB object.

You can also use the next methods:

• get_object() - returns an internal Collection object. It is not necessary for most of use cases.

• get_filtered_records(filter_data=None, use_pagination=True, ordering=None) - returns a dictionary with filtered records and additional metadata. Use filter_data if you need filter records with specified filters. If filter_data=None system uses GET parameters from current request. Use use_pagination=False for completely disable pagination feature. Do not use it for large data sets because it cause dramatically slow down your system. Use ordering attribute for specify ordering rules. By default, all records are ordering by name property. If you have, for example, property with name position and want to sort with it, you should use ordering='+prop_position' for it. If you have Choice property with name floor and want to sort records related to the choice options position, you should use ordering='+sort_index_floor'.

• get_first_filtered(**kwargs) - works like get_filtered_records with the same attributes but returns only a first record or None if it does not exist.

• get_record_by_id(id) - returns an internal Record object for a record with specified id. Raises an error if the record does not exist.

• get_record(key, or404=False) - returns an internal Record object for a record with specified key. If or404=True force system to show "404 Not found" error page with proper status if record does not exist. By default, raises a server error.

• create_record(record_type_key=None, name=None, properties=None, parent_key=None, subset_keys=None) - creates a new record in particular collection. Attribute record_type_key is using for collection with multiple record types support for specify which exact type of record you want to create. In most cases you should specify name attribute as a dictionary with language code as a key. For example, name={'en':'Sample record'}. You may ignore this attribute if name is a computed property for current record type. In such case name will be generated automatically based on specified computation template. Record properties are using in the system internal format as a dictionary. So, please check information about internal representation for different property types for doing it right. If you want to create a child record for another record, you should specify parent_key attribute. Init subset_keys with a list of record keys if you are using subsets support.

• get_queryset() - returns an internal QuerySet object for working with Record objects on the low level. You should not use this method in most of the use cases.

• get_record_form(record_type_key=None, record=None, use_request_data='auto', languages=None, ignore_name=False, property_keys=None, widgets_override=None, prefix='') - returns a RecordForm object for rendering, validating and submitting a record form. If you are using collection with multiple record types support you should specify the exact type. For the purposes of editing existing record you should specify it in record attribute. It should be an object of type Record (not its dictionary or json representation). Attribute use_request_data supports a few different values: True if you want to use data from POST request for initializing and validation, False if you do not need it and 'auto' (common case) if you need to do form validation if particular request method is POST, or not if GET. Attribute languages is using for limit multilingual fields to work only with particular languages in multilingual configurations. For example, if your system supports English and Dutch, but you want to show only English version of record name field you should use languages=['en']. If name is a computed property you should use ignore_name=True for hide Name input field from the form. For managing other record properties visibility use property_keys attribute. Specify a list of property keys you want to be included to the form. Some properties (different type of relations for example) support different alternate widgets. If you need to use a widget which is differ from one using in management console you can use widgets_override attribute. For example, widgets_override={'gender':'radio_select'}. In cases, when need to show many copies of a one single form and work with them your should use prefix attribute for differentiate data from different form instances.

• get_record_type(key) - returns an internal RecordType object with specified key.

• get_property(record_type_key, property_key) - returns an internal RecordProperty object with particular key for specified record type.

• get_property_option(record_type_key, property_key, option_key) - returns an internal PropertyOption object for specified property option.

• get_scenario(key) - returns an internal Scenario object with specified key. You can use it for some advanced cases with automation.

• get_button(key) - returns an internal ActionButton object with specified case. It is also could be used for some advanced use cases.

Simple queries with filtering via URL parameters

You can use DB interface for querying records from the database. Parameters passed from URL can are using for filtering data. Look at the example. Here we have the collection with key students and we want to get all records with 20 records per page. The number of active page is passing via GET parameter:

{% set students = DB(request)
    .get_collection(
        'students', 
        records_per_page=20, 
        use_page_number_param='page')
            .get_filtered_records() %}

So, if we have a controller which is opening the template on URL /students/ we can go to /students/?page=1 (if we ignore page parameter it will mean that page=1). In such case, variable vacancies will contain the next data (it is an example):

{
    'manage_language': 'en', 
    'records': [
        <Record: Angel Henry>, 
        ...
        <Record: Diane Russell>,
    ], 
    'show_property_filters': True, 
    'search_query': '', 
    'filters': [
        {
            'template': 'multiple_choice', 
            'f_obj': <RecordProperty: Classes>, 
            'fid': 'dbf3a885-6e5a-4923-9a49-1724098d60bc', 
            'title': 'Classes', 
            'key': 'classes', 
            'items': [
                {
                    'key': 'class-french-at-the-evening', 
                    'doc_count': 10, 
                    'selected': False
                }, 
                ...
            ], 
            'selected': False
        }, 
        {
            'template': 'multiple_choice', 
            'f_obj': <RecordProperty: User>, 
            'fid': 'cd8215c8-1d96-46bb-9d57-424a477ea000', 
            'title': 'User', 
            'key': 'user', 
            'items': [
                {
                    'key': '[e-mail]', 
                    'doc_count': 1, 
                    'selected': False
                }
            ], 
            'selected': False
        }
    ], 
    'filtered': False, 
    'total': 30, 
    'page': 1, 
    'show_paginator': True, 
    'paginator': [
        {'number': 1}, 
        {'number': 2}
    ], 
    'last_page': 2
}

As you see attribute records contains a list of records. Also you have a list of additional attributes with meta information:

• show_property_filters - True if the collection has filtered properties.

• search_query - value of the &search=... parameter.

• filtered = True if page URL contains filtering attributes or False if not.

• total - total number of filtered records.

• page - current page.

• show_paginator - True if number of pages is more than one.

• paginator - a list of objects with page numbers (maybe some extra info in future).

• last_page - a number of last page.

As you can see we also have two filters, for properties Classes and User. Note, that get_filtered_records returns filters blocks not only for filtered properties but also for all indexed properties like relations ones.

If you try to change the URL to something like /students/?f-classes=class-french-at-the-evening&search=beth the same template tag will return completely different result:

{
    'manage_language': 'en', 
    'records': [<Record: Beth Bell>], 
    'show_property_filters': True, 
    'search_query': 'beth', 
    'filters': [
        {
            'template': 'multiple_choice', 
            'f_obj': <RecordProperty: Classes>, 
            'fid': 'dbf3a885-6e5a-4923-9a49-1724098d60bc', 
            'title': 'Classes', 
            'key': 'classes', 
            'items': [
                {
                    'key': 'class-french-at-the-evening', 
                    'doc_count': 1, 
                    'selected': True
                }
            ], 
            'selected': True
        }, 
        {
            'template': 'multiple_choice', 
            'f_obj': <RecordProperty: User>, 
            'fid': 'cd8215c8-1d96-46bb-9d57-424a477ea000', 
            'title': 'User', 
            'key': 'user', 
            'items': [], 
            'selected': False
        }
    ], 
    'filtered': True, 
    'total': 1, 
    'page': 1, 
    'show_paginator': False, 
    'paginator': [], 
    'last_page': 1
}

So, as you can see, we can use the same template for generating filtered list of records which support dynamic filters. See how we can show retrieved data on the page:

{% from "BASE:macroses/collections.html" import 
                filters_block, paginator with context %}

<div class="row">
    <div class="col-lg-8">
        {% for student in students.records %}
        <div class="card mb-4">
            <div class="card-body">
                <a href="/students/{{ student.key }}/">
                    {{ student.name.en }}</a>
                <div class="mt-2">
                    {% with s_dict=student.get_dict(
                            include_related=['classes']) %}
                    <ul>
                        {% for c_dict in s_dict.properties.classes %}
                        <li>
                            {{c_dict.name.en}} : 
                            {{c_dict.properties.teacher.name.en}}
                        </li>
                        {% endfor %}
                    </ul>
                    {% endwith %}
                </div>
            </div>
        </div>
        {% else %}
            <div class="alert alert-info">No students found</div>
        {% endfor %}
        
        {% if students.show_paginator %}
            {{ paginator(students) }}
        {% endif %}
    </div>
    <div class="col-lg-4">
        <form method="get">
            {{ filters_block(students.filters) }}
            
            <button type="submit" 
                class="btn btn-primary">Search</button>
        </form>
    </div>
</div>

Simple queries with filtering via attributes

In many cases you need to query some records from the database with some strict filters set which cannot be modified via URL parameters. In such case you can use something like that:

{% set similar_jobs=DB(request)
    .get_collection('jobs')
    .get_filtered_records(
        filter_data={'f-tags':j_dict.properties.tags}) %}

We also can set how much records we want to get:

{% set jobs = DB(request)
    .get_collection('jobs', records_per_page=9)
    .get_filtered_records(filter_data={}) %}

Another example should be useful when we have some collection which implements a some part of a user profile. In such case we often need get only first record from the result or None. That is because in most cases we are using one-to-one relation. Look at the two different solutions:

{# Query records and than getting first one #}
{% set candidates = DB()
    .get_collection('candidates')
    .get_filtered_records(
        filter_data={'f-user': [request.user.email]}).records %}
{% set candidate = candidates[0] if candidates else None %}

{# More compact version #}
{% set candidate = DB()
    .get_collection('candidates')
    .get_first_filtered(
        filter_data={'f-user': [request.user.email]}) %}

Dictionary representation for a record

Filtering results contain a list of Record objects. Such objects contain all properties values but not all information you can get directly. For example, if you read a record properties attribute you can see something like that:

{
    'bday': '1989-10-15', 
    'email': '[e-mail]', 
    'phone': '[phone]', 
    'classes': ['class-french-at-the-evening']
}

Here you can see an internal representation of some types of properties. For example, dates are storing as strings in format YYYY-MM-DD and many-to-many relations are using lists of related record keys. It is pretty compact form for storing data in the database but not convinient in most cases to use on site pages (or in other platform components when you want to access your data). Of course, you also can work with other attributes of Record object, like name, etc., but it is still pretty hard. For that purposes, you can use method .get_dict() which returns much more complex structure:

{
    'language': 'en', 
    'name': {'en': 'Beth Bell'}, 
    'id': '0ab4dc51-d940-44b0-baf1-adf938fddc3b', 
    'key': 'student-beth-bell', 
    'record_type': {
        'name': {'en': 'Student'}, 
        'key': 'student', 
        'nested_records_support': False, 
        'children_support_children': False, 
        'attached_images_support': True
    }, 
    'children': {'count': 0}, 
    'record_subsets': [], 
    'attached_images': [
        {
            'name': {'en': 'Beth Bell'}, 
            'image': 'images/records/0ab4dc51-d940-44b0-baf1-adf938fddc3b/0245b59421ac47d8a2635ca62ebf0fe9.png', 
            'url': 'https://demo-tuts.tabbli.com/media/images/records/0ab4dc51-d940-44b0-baf1-adf938fddc3b/0245b59421ac47d8a2635ca62ebf0fe9.png'
        }
    ], 
    'important_properties': [
        'bday', 
        'email', 
        'phone', 
        'classes', 
        'user'
    ], 
    'filtered_properties': ['classes'], 
    'multilingual_properties': [], 
    'property_types': {
        'bday': 'date', 
        'email': 'email', 
        'phone': 'string', 
        'classes': 'many_to_many', 
        'user': 'user_relation_unique'
    }, 
    'property_names': {
        'bday': {'en': 'Date of birthday'}, 
        'email': {'en': 'Email'}, 
        'phone': {'en': 'Phone'}, 
        'classes': {'en': 'Classes'}, 
        'user': {'en': 'User'}
    }, 
    'properties': {
        'bday': datetime.datetime(1989, 10, 15, 0, 0), 
        'email': '[e-mail]', 
        'phone': '[phone]', 
        'classes': [
            {
                'name': {'en': 'French at the Evening'}, 
                'id': '93788a1d-3071-4dea-bcb2-e98a635cf7c3', 
                'key': 'class-french-at-the-evening'
            }
        ]
    }
}

As you can see, here "Date of birthday" property represents as a datetime object and list of related classes contains information about related records names and ids. Sometimes you need to query some related records to the template. In such case you can use get_record method for getting a record with particular record key:

{% set company=DB()
    .get_collection('companies')
    .get_record(key=j_dict.properties.company.key) %}

Or you can use include_related optional attribute for get_dict method for prefetch additional data to the result. This attribute must be a list of collections name the record is related with. So if you use {% set s_dict=student.get_dict(include_related=['classes']) %} the result will be like this:

{
    'language': 'en', 
    'name': {'en': 'Beth Bell'}, 
    'id': '0ab4dc51-d940-44b0-baf1-adf938fddc3b', 
    'key': 'student-beth-bell', 
    'record_type': {
        'name': {'en': 'Student'}, 
        'key': 'student', 
        'nested_records_support': False, 
        'children_support_children': False, 
        'attached_images_support': True
    }, 
    'children': {'count': 0}, 
    'record_subsets': [], 
    'attached_images': [
        {
            'name': {'en': 'Beth Bell'}, 
            'image': 'images/records/0ab4dc51-d940-44b0-baf1-adf938fddc3b/0245b59421ac47d8a2635ca62ebf0fe9.png', 
            'url': 'https://demo-tuts.tabbli.com/media/images/records/0ab4dc51-d940-44b0-baf1-adf938fddc3b/0245b59421ac47d8a2635ca62ebf0fe9.png'
        }
    ], 
    'important_properties': [
        'bday', 
        'email', 
        'phone', 
        'classes', 
        'user'
    ], 
    'filtered_properties': ['classes'], 
    'multilingual_properties': [], 
    'property_types': {
        'bday': 'date', 
        'email': 'email', 
        'phone': 'string', 
        'classes': 'many_to_many', 
        'user': 'user_relation_unique'
    }, 
    'property_names': {
        'bday': {'en': 'Date of birthday'}, 
        'email': {'en': 'Email'}, 
        'phone': {'en': 'Phone'}, 
        'classes': {'en': 'Classes'}, 
        'user': {'en': 'User'}
    }, 
    'properties': {
        'bday': datetime.datetime(1989, 10, 15, 0, 0), 
        'email': '[e-mail]', 
        'phone': '[phone]', 
        'classes': [
            {
                'language': 'en', 
                'name': {'en': 'French at the Evening'}, 
                'id': '93788a1d-3071-4dea-bcb2-e98a635cf7c3', 
                'key': 'class-french-at-the-evening', 
                'record_type': {
                    'name': {'en': 'Class'}, 
                    'key': 'class', 
                    'nested_records_support': False, 
                    'children_support_children': False, 
                    'attached_images_support': False
                }, 
                'children': {'count': 0}, 
                'record_subsets': [], 
                'attached_images': [], 
                'important_properties': [
                    'course', 
                    'teacher', 
                    'date', 
                    'up_to', 
                    'time',
                    'days'
                ], 
                'filtered_properties': [
                    'date', 
                    'up_to', 
                    'time', 
                    'days'
                ], 
                'multilingual_properties': [], 
                'property_types': {
                    'course': 'one_to_many', 
                    'teacher': 'one_to_many', 
                    'date': 'date', 
                    'up_to': 'date', 
                    'time': 'string', 
                    'days': 'string'
                }, 
                'property_names': {
                    'course': {'en': 'Course'}, 
                    'teacher': {'en': 'Teacher'}, 
                    'date': {'en': 'Date of starting'}, 
                    'up_to': {'en': 'Up to'}, 
                    'time': {'en': 'Time'}, 
                    'days': {'en': 'Days of week'}
                }, 
                'properties': {
                    'course': {
                        'name': {'en': 'French Language'}, 
                        'id': '72dd063e-80de-49f0-8fc8-6ce3722523a5', 
                        'key': 'course-french-language'
                    }, 
                    'teacher': {
                        'name': {'en': 'Julie Henry'}, 
                        'id': '2eef993e-70cd-4560-8d70-d45e5707093e', 
                        'key': 'person-julie-henry'
                    }, 
                    'date': datetime.datetime(2019, 11, 4, 0, 0), 
                    'up_to': datetime.datetime(2020, 5, 1, 0, 0), 
                    'time': '16:00 - 18:00', 
                    'days': 'Mon, Wed'
                }
            }
        ]
    }
}

As you see, here property classes contains a full information about related records. Use this feature carefully because you increase your resouces usage in such case.

New records creation

Tabbli supports two kinds of solution for creation new records in the database:

• With create_record(...) method.

• With a form's save() method.

If you need to just create new record in non-interactive mode better to use create_record method. For example, here we are trying to create student profile if it is not available yet for a current user (we can put this code somewhere in the base template):

{% set student = DB()
    .get_collection('students')
    .get_first_filtered(
        filter_data={'f-user': [request.user.email]}) %}
{% if not student %}
    {% set student = DB()
        .get_collection('students')
        .create_record(
            properties={
                'user':request.user.email, 'active':True}) %}
{% endif %}

Or, if we want to implement an interface where user can create a some record (like Job record) with filling the form we can use the next code:

{# Get the form object with necessary fields #}
{% set form = DB(request)
    .get_collection('jobs')
    .get_record_form(property_keys=[
        'category', 
        'min_salary', 
        'short_description', 
        'full_description', 
        'type', 
        'tags']) %}
        
{% if request.method == "POST" %}
    {# This block will launched after the form submission #}
    {% if form.is_valid() %}
        {# Use commit=False if we need to modify data befor saving #}
        {% set job = form.save(commit=False) %}
        {% do job.properties.update(
            {'author':request.user.email, 'active':True}) %}
        {# Save data to the database #}
        {% do job.save() %}
        {# Trigger automation scenarios #}
        {% do job.execute_scenario('added') %}
        {% set form = None %}
    {% endif %}
{% endif %}

{% if form %}
    {# Render the form after page opening or validation errors #}
    <div class="container">
        <h1 class="h2 mt-4 mb-4">Post job</h1>
        <form method="post">
            {% from "BASE:macroses/forms.html" 
                    import form_fields with context %}
            {{ form_fields(form) }}
            <div class="form-group mt-4 pt-4">
                <button type="submit" 
                    class="btn btn-success">Submit</button>
            </div>
        </form>
    </div>
{% else %}
    {# Redirect to another page after successful submission #}
    {% do redirect_to("/jobs/"+job.key+"/") %}
{% endif %}

If the form contains all necessary data and you do not need to modify it before saving to the database, you don't need to use commit=False attribute in the form save() method. You can just use {% set job = form.save() %} for write data directly to the database.