Defining a Model¶

class User(Model):
    pass

Note that we did not tell the ORM which table to use for the User model. The plural “snake case” name of the class name will be used as the table name unless another name is explicitly specified. In this case, the ORM will assume the User model stores records in the users table. You can specify a custom table by defining a __table__ property on your model:

class User(Model):

    __table__ = 'my_users'

Once a model is defined, you are ready to start retrieving and creating records in your table. Note that you will need to place updated_at and created_at columns on your table by default. If you do not wish to have these columns automatically maintained, set the __timestamps__ property on your model to False.

orator make:model User

orator make:model User -m

Retrieving all models¶

users = User.all()

Retrieving a record by primary key¶

user = User.find(1)

print(user.name)

Retrieving a Model by primary key or raise an exception¶

Sometimes it may be useful to throw an exception if a model is not found. You can use the find_or_fail method for that, which will raise a ModelNotFound exception.

model = User.find_or_fail(1)

model = User.where('votes', '>', 100).first_or_fail()

Querying using models¶

users = User.where('votes', '>', 100).take(10).get()

for user in users:
    print(user.name)

Aggregates¶

You can also use the query builder aggregate functions:

count = User.where('votes', '>', 100).count()

If you feel limited by the builder’s fluent interface, you can use the where_raw method:

users = User.where_raw('age > ? and votes = 100', [25]).get()

Chunking Results¶

If you need to process a lot of records, you can use the chunk method to avoid consuming a lot of RAM:

for users in User.chunk(100):
    for user in users:
        # ...

Specifying the query connection¶

You can specify which database connection to use when querying a model by using the on method:

user = User.on('connection-name').find(1)

If you are using Read / Write connections, you can force the query to use the “write” connection with the following method:

user = User.on_write_connection().find(1)

Defining fillable attributes on a model¶

The __fillable__ property specifies which attributes can be mass-assigned.

class User(Model):

    __fillable__ = ['first_name', 'last_name', 'email']

Defining guarded attributes on a model¶

The __guarded__ is the inverse and acts as “blacklist”.

class User(Model):

    __guarded__ = ['id', 'password']

You can also block all attributes from mass-assignment:

__guarded__ = ['*']

Saving a new model¶

To create a new record in the database, simply create a new model instance and call the save method.

user = User()

user.name = 'John'

user.save()

You can also use the create method to save a model in a single line, but you will need to specify either the __fillable__ or __guarded__ property on the model since all models are protected against mass-assigment by default.

After saving or creating a new model with auto-incrementing IDs, you can retrieve the ID by accessing the object’s id attribute:

inserted_id = user.id

Using the create method¶

# Create a new user in the database
user = User.create(name='John')

# Retrieve the user by attributes, or create it if it does not exist
user = User.first_or_create(name='John')

# Retrieve the user by attributes, or instantiate it if it does not exist
user = User.first_or_new(name='John')

Updating a retrieved model¶

user = User.find(1)

user.name = 'Foo'

user.save()

You can also run updates as queries against a set of models:

affected_rows = User.where('votes', '>', 100).update(status=2)

Saving a model and relationships¶

Sometimes you may wish to save not only a model, but also all of its relationships. To do so, you can use the push method:

user.push()

Deleting an existing model¶

To delete a model, simply call the delete model:

user = User.find(1)

user.delete()

Deleting an existing model by key¶

User.destroy(1)

User.destroy(1, 2, 3)

You can also run a delete query on a set of models:

affected_rows = User.where('votes', '>' 100).delete()

Updating only the model’s timestamps¶

If you want to only update the timestamps on a model, you can use the touch method:

user.touch()

Forcing soft deleted models into results¶

To force soft deleted models to appear in a result set, use the with_trashed method on the query:

users = User.with_trashed().where('account_id', 1).get()

The with_trashed method may be used on a defined relationship:

user.posts().with_trashed().get()

One To One¶

from orator.orm import has_one


class User(Model):

    @has_one
    def phone(self):
        return Phone

phone = User.find(1).phone

SELECT * FROM users WHERE id = 1

SELECT * FROM phones WHERE user_id = 1

@has_one('foreign_key')
def phone(self):
    return Phone

@has_one('foreign_key', 'local_key')
def phone(self):
    return Phone

from orator.orm import belongs_to


class Phone(Model):

    @belongs_to
    def user(self):
        return User

@belongs_to('local_key')
def user(self):
    return User

@belongs_to('local_key', 'parent_key')
def user(self):
    return User

One To Many¶

An example of a one-to-many relation is a blog post that has many comments:

from orator.orm import has_many


class Post(Model):

    @has_many
    def comments(self):
        return Comment

Now you can access the post’s comments via Dynamic properties:

comments = Post.find(1).comments

Again, you may override the conventional foreign key by passing a first argument to the has_many decorator. And, like the has_one relation, the local column may also be specified:

@has_many('foreign_key')
def comments(self):
    return Comment

@has_many('foreign_key', 'local_key')
def comments(self):
    return Comment

from orator.orm import belongs_to

class Comment(Model):

    @belongs_to
    def post(self):
        return Post

Many To Many¶

Many-to-many relations are a more complicated relationship type. An example of such a relationship is a user with many roles, where the roles are also shared by other users. For example, many users may have the role of “Admin”. Three database tables are needed for this relationship: users, roles, and roles_users. The roles_users table is derived from the alphabetical order of the related table names, and should have the user_id and role_id columns.

We can define a many-to-many relation using the belongs_to_many decorator:

from orator.orm import belongs_to_many


class User(Model):

    @belongs_to_many
    def roles(self):
        return Role

Now, we can retrieve the roles through the User model:

roles = User.find(1).roles

If you want to use an unconventional table name for your pivot table, you can pass it as the first argument to the belongs_to_many method:

@belongs_to_many('user_role')
def roles(self):
    return Role

You can also override the conventional associated keys:

@belongs_to_many('user_role', 'user_id', 'foo_id')
def roles(self):
    return Role

Of course, you also can define the inverse of the relationship on the Role model:

class Role(Model):

    @belongs_to_many
    def users(self):
        return User

Has Many Through¶

The “has many through” relation provides a convenient short-cut for accessing distant relations via an intermediate relation. For example, a Country model might have many Post through a User model. The tables for this relationship would look like this:

countries
    id - integer
    name - string

users
    id - integer
    country_id - integer
    name - string

posts
    id - integer
    user_id - integer
    title - string

Even though the posts table does not contain a country_id column, the has_many_through relation will allow access to a country’s posts via country.posts:

from orator.orm import has_many_through


class Country(Model):

    @has_many_through(User)
    def posts(self):
        return Post

If you want to manually specify the keys of the relationship, you can pass them as the second and third arguments to the decorator:

@has_many_through(User, 'country_id', 'user_id')
def posts(self):
    return Post

Polymorphic relations¶

Polymorphic relations allow a model to belong to more than one other model, on a single association. For example, you might have a Photo model that belongs to either a Staff model or an Order model.

from orator.orm import morph_to, morph_many


class Photo(Model):

    @morph_to
    def imageable(self):
        return


class Staff(Model):

    @morph_many('imageable')
    def photos(self):
        return Photo


class Order(Model):

    @morph_many('imageable')
    def photos(self):
        return Photo

staff = Staff.find(1)

for photo in staff.photos:
    # ...

photo = Photo.find(1)

imageable = photo.imageable

staff
    id - integer
    name - string

orders
    id - integer
    price - integer

photos
    id - integer
    path - string
    imageable_id - integer
    imageable_type - string

class Order(Model):

    __morph_name__ = 'order'

Many To Many polymorphic relations¶

posts
    id - integer
    name - string

videos
    id - integer
    name - string

tags
    id - integer
    name - string

taggables
    tag_id - integer
    taggable_id - integer
    taggable_type - string

class Post(Model):

    @morph_to_many('taggable')
    def tags(self):
        return Tag

class Tag(Model):

    @morphed_by_many('taggable')
    def posts(self):
        return Post

    @morphed_by_many('taggable')
    def videos(self):
        return Video

class User(Model):

    @has_many
    def comments(self):
        return (
            Comment
                .select('id', 'title', 'user_id')
                .order_by('created_at', 'desc')
        )

Querying relations when selecting¶

When accessing the records for a model, you may wish to limit the results based on the exeistence of a relationship. For example, you may wish to retrieve all blog posts that have at least one comment. To actually do so, you can use the has method:

posts = Post.has('comments').get()

This would execute the following SQL query:

SELECT * FROM posts
WHERE (
    SELECT COUNT(*) FROM comments
    WHERE comments.post_id = posts.id
) >= 1

You can also specify an operator and a count:

posts = Post.has('comments', '>', 3).get()

This would execute:

SELECT * FROM posts
WHERE (
    SELECT COUNT(*) FROM comments
    WHERE comments.post_id = posts.id
) > 3

Nested has statements can also be constructed using “dot” notation:

posts = Post.has('comments.votes').get()

And the corresponding SQL query:

SELECT * FROM posts
WHERE (
    SELECT COUNT(*) FROM comments
    WHERE comments.post_id = posts.id
    AND (
        SELECT COUNT(*) FROM votes
        WHERE votes.comment_id = comments.id
    ) >= 1
) >= 1

If you need even more power, you can use the where_has and or_where_has methods to put “where” conditions on your has queries:

posts = Post.where_has(
    'comments',
    lambda q: q.where('content', 'like', 'foo%')
).get()

Dynamic properties¶

The Orator ORM allows you to access your relations via dynamic properties. It will automatically load the relationship for you. It will then be accessible via a dynamic property by the same name as the relation. For example, with the following model Post:

class Phone(Model):

    @belongs_to
    def user(self):
        return User

phone = Phone.find(1)

You can then print the user’s email like this:

print(phone.user.email)

Now, for one-to-many relationships:

class Post(Model):

    @has_many
    def comments(self):
        return Comment

post = Post.find(1)

You can then access the post’s comments like this:

comments = post.comments

If you need to add further constraints to which comments are retrieved, you may call the comments method and continue chaining conditions:

comments = post.comments().where('title', 'foo').first()

Eager load constraints¶

Sometimes you may wish to eager load a relationship but also specify a condition for the eager load. Here’s an example:

users = User.with_({
    'posts': Post.query().where('title', 'like', '%first%')
}).get()

In this example, we’re eager loading the user’s posts only if the post’s title contains the word “first”.

You can also use a callback:

users = User.with_({
    'posts': lambda q: q.where('title', 'like', '%first%').order_by('created_at', 'desc')
})

Lazy eager loading¶

It is also possible to eagerly load related models directly from an already existing model collection. This may be useful when dynamically deciding whether to load related models or not, or in combination with caching.

books = Book.all()

books.load('author', 'publisher')

You can also pass conditions:

books.load({
   'author': Author.query().where('name', 'like', '%foo%')
})

Associating models (Belongs To)¶

When updatings a belongs_to relationship, you can use the associate method:

account = Account.find(1)

user.account().associate(account)

user.save()

Inserting related models (Many to Many)¶

You can also insert related models when working with many-to-many relationship. For example, with User and Roles models:

user = User.find(1)
role = Roles.find(3)

user.roles().attach(role)

# or
user.roles().attach(3)

user.roles().attach(3, {'expires': expires})

user.roles().detach(3)

user = User.find(1)

user.roles().detach([1, 2, 3])

user.roles().attach([{1: {'attribute1': 'value1'}}, 2, 3])

user.roles().sync([1, 2, 3])

user.roles().sync([{1: {'expires': True}}])

role = Role(name='Editor')

User.find(1).roles().save(role)

User.find(1).roles().save(role, {'expires': True})

Deleting records on a pivot table¶

To delete all records on the pivot table for a model, you can use the detach method:

User.find(1).roles().detach()

Updating a record on the pivot table¶

Sometimes you may need to update your pivot table, but not detach it. If you wish to update your pivot table in place you may use update_existing_pivot method like so:

User.find(1).roles().update_existing_pivot(role_id, attributes)

Providing a custom timestamp format¶

If you whish to customize the format of your timestamps (the default is the ISO Format) that will be returned when using the serialize or the to_json methods, you can override the get_date_format method:

class User(Model):

    def get_date_format(self):
        return 'DD-MM-YY'

Defining a query scope¶

Scopes allow you to easily re-use query logic in your models. To define a scope, simply use the scope decorator:

from orator.orm import scope


class User(Model):

    @scope
    def popular(self, query):
        return query.where('votes', '>', 100)

    @scope
    def women(self, query):
        return query.where_gender('W')

Using a query scope¶

users = User.popular().women().order_by('created_at').get()

Dynamic scopes¶

Sometimes you may wish to define a scope that accepts parameters. Just add your parameters to your scope function:

class User(Model):

    @scope
    def of_type(self, query, type):
        return query.where_type(type)

Then pass the parameter into the scope call:

users = User.of_type('member').get()

Using mixins¶

Sometimes you may wish to define a scope that applies to all queries performed on a model. In essence, this is how Orator’s own “soft delete” feature works. Global scopes can be defined using a combination of mixins and an implementation of the Scope class.

First, let’s define a mixin. For this example, we’ll use the SoftDeletes that ships with Orator:

from orator import SoftDeletingScope


class SoftDeletes(object):

    @classmethod
    def boot_soft_deletes(cls, model_class):
        """
        Boot the soft deleting mixin for a model.
        """
        model_class.add_global_scope(SoftDeletingScope())

If an Orator model inherits from a mixin that has a method matching the boot_name_of_trait naming convention, that mixin method will be called when the Orator model is booted, giving you an opportunity to register a global scope, or do anything else you want. A scope must be an instance of the Scope class, which specify an apply method.

The apply method receives an Builder query builder object and the Model it’s applied to, and is responsible for adding any additional where clauses that the scope wishes to add. So, for our SoftDeletingScope, it would look something like this:

from orator import Scope


class SoftDeletingScope(Scope):

    def apply(self, builder, model):
        """
        Apply the scope to a given query builder.

        :param builder: The query builder
        :type builder: orator.orm.builder.Builder

        :param model: The model
        :type model: orator.orm.Model
        """
        builder.where_null(model.get_qualified_deleted_at_column())

Using a scope directly¶

Let’s take the example of an ActiveScope class:

from orator import Scope


class ActiveScope(Scope):

    def apply(self, builder, model):
        return builder.where('active', 1)

You can now override the _boot() method of the model to apply the scope:

class User(Model):

    @classmethod
    def _boot(cls):
        cls.add_global_scope(ActiveScope())

        super(User, cls)._boot()

Using a callable¶

Global scopes can also be set using callables:

class User(Model):

    @classmethod
    def _boot(cls):
        cls.add_global_scope('active_scope', lambda query: query.where('active', 1))

        cls.add_global_scope(lambda query: query.order_by('name'))

        super(User, cls)._boot()

As you can see, you can directly pass a function or specify an alias for the global scope to remove it more easily later on.

Defining an accessor¶

Simply use the accessor decorator on your model to declare an accessor:

from orator.orm import Model, accessor


class User(Model):

    @accessor
    def first_name(self):
        first_name = self.get_raw_attribute('first_name')

        return first_name[0].upper() + first_name[1:]

In the example above, the first_name column has an accessor.

Defining a mutator¶

Mutators are declared in a similar fashion:

from orator.orm import Model, mutator


class User(Model):

    @mutator
    def first_name(self, value):
        self.set_raw_attribute('first_name', value.lower())

from orator.orm import Model, accessor


class User(Model):

    @accessor
    def first_name(self):
        first_name = self.get_raw_attribute('first_name')

        return first_name[0].upper() + first_name[1:]

    @first_name.mutator
    def set_first_name(self, value):
        self.set_raw_attribute('first_name', value.lower())

from orator.orm import Model, mutator


class User(Model):

    @mutator
    def first_name(self, value):
        self.set_raw_attribute('first_name', value.lower())

    @first_name.accessor
    def get_first_name(self):
        first_name = self.get_raw_attribute('first_name')

        return first_name[0].upper() + first_name[1:]

Cancelling save operations via events¶

If False is returned from the creating, updating, saving, or deleting events, the action will be cancelled:

User.creating(lambda user: user.is_valid())

Converting a model to a dictionary¶

When building JSON APIs, you may often need to convert your models and relationships to dictionaries or JSON. So, Orator includes methods for doing so. To convert a model and its loaded relationship to a dictionary, you may use the serialize method:

user = User.with_('roles').first()

return user.serialize()

Note that entire collections of models can also be converted to dictionaries:

return User.all().serialize()

Converting a model to JSON¶

To convert a model to JSON, you can use the to_json method:

return User.find(1).to_json()

Hiding attributes from dictionary or JSON conversion¶

Sometimes you may wish to limit the attributes that are included in you model’s dictionary or JSON form, such as passwords. To do so, add a __hidden__ property definition to you model:

class User(model):

    __hidden__ = ['password']

Alternatively, you may use the __visible__ property to define a whitelist:

__visible__ = ['first_name', 'last_name']

Appendable attributes¶

Occasionally, you may need to add dictionary attributes that do not have a corresponding column in your database. To do so, simply define an accessor for the value:

class User(Model):

    @accessor
    def is_admin(self):
        return self.get_raw_attribute('admin') == 'yes'

Once you have created the accessor, just add the value to the __appends__ property on the model:

class User(Model):

    __append__ = ['is_admin']

    @accessor
    def is_admin(self):
        return self.get_raw_attribute('admin') == 'yes'

Once the attribute has been added to the __appends__ list, it will be included in both the model’s dictionary and JSON forms. Attributes in the __appends__ list respect the __visible__ and __hidden__ configuration on the model.