### Navigation

- index
- modules |
- next |
- previous |
- Camelot documentation »
- Camelot Documentation contents »
- Camelot Documentation »
- Creating models »

# Views

Traditionally, in database land, views are queries defined at the database level that act like read-only tables. They allow reuse of common queries across an application, and are very suitable for reporting.

Using SQLAlchemy this traditional approach can be used, but a more dynamic approach is possible as well. We can map arbitrary queries to an object, and then visualize these objects with Camelot.

## The model to start from

In the example movie project, we can take three parts of the model : Person, Movie and VisitorReport:

    class Person( Party ):
        """Person represents natural persons
        """
        using_options( tablename = 'person', inheritance = 'multi' )
        first_name = Field( Unicode( 40 ), required = True )
        last_name = Field( Unicode( 40 ), required = True )

There is a relation between Person and Movie through the director attribute:

    class Movie(Entity):
        using_options(tablename='movies')
        title = Field(Unicode(60), required=True)
        short_description = Field(Unicode(512))
        releasedate = Field(Date)
        #
        # All relation types are covered with their own editor
        #
        director = ManyToOne('Person')
        cast = OneToMany('Cast')
        visitor_reports = OneToMany('VisitorReport')
        tags = ManyToMany('Tag')
        genre = Field(Unicode(15))
        rating = Field(camelot.types.Rating())

And a relation between Movie and VisitorReport:

    class VisitorReport(Entity):
        using_options(tablename='visitor_report')
        movie = ManyToOne('Movie', required=True)
        date = Field(Date, required=True, default=datetime.date.today)
        visitors = Field(Integer, required=True, default=0)

## Definition of the view

Suppose, we now want to display a table with the total numbers of visitors for all movies of a director.

We first define a plain old Python class that represents the expected results :

    class VisitorsPerDirector(object):

        class Admin(EntityAdmin):
            verbose_name = _('Visitors per director')
            list_display = ['first_name', 'last_name', 'visitors']

Then define a function that maps the query that calculates those results to the plain old Python object :

    def setup_views():
        from sqlalchemy.sql import select, func, and_
        from sqlalchemy.orm import mapper

        from camelot.model.authentication import Person
        from camelot_example.model import Movie, VisitorReport

        s = select([Person.party_id,
                    Person.first_name.label('first_name'),
                    Person.last_name.label('last_name'),
                    func.sum( VisitorReport.visitors ).label('visitors'),],
                    whereclause = and_( Person.party_id == Movie.director_party_id,
                                        Movie.id == VisitorReport.movie_id),
                    group_by = [ Person.party_id,
                                 Person.first_name,
                                 Person.last_name ] )

        s=s.alias('visitors_per_director')

        mapper( VisitorsPerDirector, s, always_refresh=True )

Put all this in a file called view.py

## Put into action

Then make sure the plain old Python object is mapped to the query, just after the Elixir model has been setup, by modifying the setup_model function in settings.py:

        def setup_model():
            import camelot.model
            import camelot_example.model
            from elixir import setup_all
            setup_all(create_tables=True)
            from camelot.model.authentication import updateLastLogin
            updateLastLogin()
            #
            # Load sample data with the fixure mechanism
            #
            from camelot_example.fixtures import load_movie_fixtures
            load_movie_fixtures()
            from camelot.core.sql import update_database_from_model
            #update_database_from_model()
            #
            # setup the views
            #
            from camelot_example.view import setup_views
            setup_views()

And add the plain old Python object to a section in the ApplicationAdmin:

        def get_sections(self):

            from camelot.model.memento import Memento
            from camelot.model.authentication import Person, Organization
            from camelot.model.i18n import Translation

            from camelot_example.model import Movie, Tag, VisitorReport
            from camelot_example.view import VisitorsPerDirector
    # begin import action
            from camelot_example.importer import ImportCovers
    # end import action

            return [
    # begin section with action
                    Section( _('Movies'),
                             self,
                             Icon('tango/22x22/mimetypes/x-office-presentation.png'),
                             items = [ Movie,
                                       Tag,
                                       VisitorReport,
                                       VisitorsPerDirector,
                                       ImportCovers() ]),
    # end section with action
                    Section( _('Relation'),
                             self,
                             Icon('tango/22x22/apps/system-users.png'),
                             items = [ Person,
                                       Organization ]),
                    Section( _('Configuration'),
                             self,
                             Icon('tango/22x22/categories/preferences-system.png'),
                             items = [ Memento,
                                       Translation ])
                    ]

### Navigation

- index
- modules |
- next |
- previous |
- Camelot documentation »
- Camelot Documentation contents »
- Camelot Documentation »
- Creating models »

Comments
blog comments powered by Disqus