Defining Models

In this section we explore resource model definition in more detail.

To define a resource model, first declare a class that inherits from Model and set the from_ attribute to an SQLAlchemy Table object:

from jsonapi.model import Model
from jsonapi.tests.db import users_t

UserModel(Model):
    from_ = users_t

By default, the primary key column is mapped to the reserved id field.

Note

Mapped tables must have single column primary keys. Composite primary key columns are not supported.

No additional attributes are required.

By default, the type of the resource is generated automatically from the model class name. If you wish to override it, you can set it using the type_ attribute:

UserModel(Model):
    type_ = 'users'
    from_ = users_t

The fields attribute can be used to define the set of attributes and relationships for the resource. To define attributes that map directly to table columns of the same name, simply list their names as string literals:

class UserModel(Model):
    type_ = 'user'
    from_ = users_t
    fields = 'email', 'created_on'

The fields email and created_on will be created and mapped to the database columns: users_t.c.email and users_t.c.created_on, respectively.

Custom Fields

The datatype of the field is determined automatically from the datatype of the database column it maps to. To override datatype auto-detection, you can explicitly set the datatype using the Field class:

from jsonapi.fields import Field
from jsonapi.datatypes import Date

class UserModel(Model):
    from_ = users_t
    fields = ('email', 'status', Field('created_on', field_type=Date))

The type of created_on field, which maps to a timestamp database column, is set to Date instead of DateTime.

To define a field that maps to a number of columns (a column expression):

class UserModel(Model):
        from_ = users_t, user_names_t
        fields = 'email', Field('name', lambda c: c.first + ' ' + c.last)

The second argument takes a function that accepts an SQLAlchemy ImmutableColumnCollection representing the join result of the mapped tables listed in the from_ attribute. The function must return a valid column expression (including function calls):

Field('age', lambda c: func.extract('year', func.age(c.birthday)), Integer))

If you wish to map a field to a database column of a different name:

class UserModel(Model):
    from_ = users_t
    fields = 'status', Field('email_address', lambda c: c.email)

You can also pass an SQLAlchemy Column explicitly. This is useful when mapping to multiple tables that share the same column names (see Multiple Tables):

class UserModel(Model):
    from_ = users_t
    Field('email-address', users_t.c.email)

Multiple Tables

A resource model can be mapped to more than one database table:

from jsonapi.tests.db import user_names_t

class UserModel(Model):
    from_ = users_t, user_names_t

In the following example, we define three fields. One maps to users_t.c.email column, and two map to user_names_t.c.first and user_names_t.c.last columns:

class UserModel(Model):
    type_ = 'user'
    from_ = users_t, user_names_t
    fields = 'email', 'first', 'last'

If a left outer join or an explicit on clause is needed, a FromItem object can be passed in place of the table object. For example:

from jsonapi import FromItem

UserModel(Model):
    from_ = users_t, FromItem(user_names_t, left=True)

Relationships

The Relationship class can be used to define relationships between resource models:

from jsonapi import ONE_TO_MANY, MANY_TO_ONE
from jsonapi.tests.db import articles_t

class UserModel(Model):
    from_ = users_t, user_names_t
    fields = ('email',
              Derived('name', lambda c: c.first + ' ' + c.last)
              Relationship('articles', 'ArticleModel',
                           ONE_TO_MANY, articles_t.c.author_id))

class ArticleModel(Model):
    from_ = articles_t
    fields = ('title', 'body', 'created_on',
              Relationship('author', 'UserModel',
                           MANY_TO_ONE, articles_t.c.author_id))

In this example, a user can author multiple article s, and an article is authored by one user. This relationship is represented by two Relationship objects, each defined in it’s respective model.

The first argument to Relationship is the name of the field. The second arguments is the target model class name. The third argument is cardinality of the relationship.

The fourth and possibly fifth arguments depend on the cardinality of the relationship. Both arguments, if provided must be SQLAlchemy Column objects that are part of a foreign key and are not a primary key of any model.

• For ONE_TO_ONE relationships, no argument is required in most cases. The only exception is when the foreign key of the relationship lives in a standalone one-to-one join table.
• For ONE_TO_MANY and MANY_TO_ONE relationships, one argument is required.
• For MANY_TO_MANY relationships, two arguments are required and the second must be the one referencing the primary key of the related resource model

Aggregate Fields

The Aggregate class can be used to define fields that map to aggregate functions:

class UserModel(Model):
    from_ = users_t

    fields = ('email', 'name', 'created_on',
              Relationship('articles', 'ArticleModel',
                           ONE_TO_MANY, articles_t.c.author_id),
              Aggregate('article_count', 'articles', sa.func.count))

Note

The second argument to Aggregate must match a name of a Relationship field defined in the same model, as shown above.