To create a new model backed by a PostgreSQL view, follow these instructions:
- Create a new model with the _auto class attribute set to False:
class LibraryBookRentStatistics(models.Model):
_name = 'library.book.rent.statistics'
_auto = False
- Declare the fields you want to see in the model, setting them as readonly:
book_id = fields.Many2one('library.book', 'Book', readonly=True)
rent_count = fields.Integer(string="Times borrowed", readonly=True)
average_occupation = fields.Integer(string="Average Occupation (DAYS)",
readonly=True)
- Define the init() method to create the view:
@api.model_cr
def init(self):
tools.drop_view_if_exists(self.env.cr, self._table)
query = """
CREATE OR REPLACE VIEW library_book_rent_statistics AS (
SELECT
min(lbr.id) as id,
lbr.book_id as book_id,
count(lbr.id) as rent_count,
avg((EXTRACT(epoch from age(return_date, rent_date)) / 86400))::int as average_occupation
FROM
library_book_rent AS lbr
JOIN
library_book as lb ON lb.id = lbr.book_id
WHERE lbr.state = 'returned'
GROUP BY lbr.book_id
);
"""
self.env.cr.execute(query)
- You can now define Views for the new model. A pivot view is especially useful to explore the data (refer to Chapter 10, Backend Views).
- Don't forget to define some access rules for the new model (take a look at Chapter 11, Access Security).