Model for View table

Hi,

So far, all tryton model class is mapped one 2 one to a DB table.
But currently I am required to access a View object (which is combination of tables through joins), see below:

CREATE VIEW afx_project_tracklist_record AS
SELECT 
    emp.employee_id AS resource_id,
    emp.name AS resource_name,
    p_p.name AS client_name,
    pro.user AS pic,
    tim.name AS team,
    pro.proj_no AS proj_no,
    pro.proj_name AS proj_name,
    pro.po_no AS po_no, 
    pro.so_no AS so_no,
    pro.start_date AS start_date,
    pro.end_date AS end_date
FROM 
    afx_project pro
LEFT JOIN 
    afx_project_member mem ON pro.id = mem.project
LEFT JOIN 
    afx_employee emp ON mem.member = emp.ref_employee_id
LEFT JOIN
    afx_team tim ON emp.team = tim.id
LEFT JOIN
    company_company com ON pro.customer = com.id
LEFT JOIN
    party_party p_p ON com.party = p_p.id;

And I try to create the python class like below:

class ProjectTrackListRecord(ModelSQL, ModelView):
    'Project Track List'
    __name__ = 'afx.project.tracklist.record'
    _table = 'afx_project_tracklist_record'  # Name of your actual DB view
    
    resource_id = fields.Char('Resource ID')
    resource_name = fields.Char('Resource Name')
    client_name = fields.Char('Client Name')
    pic = fields.Char('PIC')
    team = fields.Char('Team')
    proj_no = fields.Char('Project No')
    proj_name = fields.Char('Project Name')
    po_no = fields.Char('PO No')
    so_no = fields.Char('S/O No')
    start_date = fields.Char('Start Date')
    end_date = fields.Char('End Date')

And this produce error, well honestly I already guess it :laughing:

My question: is there a way on how to present a VIEW table in tryton model so I can present it to frontend?

Regards
Bromo

So what is the error?

Please when requesting support, provide at least all the information!

1 Like

Sorry @ced

So with the exactly the above code I got compile error (in executing trytond-admin -c trytond.conf -d tryton --all command) like below:

Traceback (most recent call last):
  File "/home/tryton/afx_project/.env/bin/trytond-admin", line 33, in <module>
    admin.run(options)
  File "/home/tryton/afx_project/.env/lib/python3.10/site-packages/trytond/admin.py", line 59, in run
    pool.init(update=options.update, lang=list(lang),
  File "/home/tryton/afx_project/.env/lib/python3.10/site-packages/trytond/pool.py", line 143, in init
    restart = not load_modules(
  File "/home/tryton/afx_project/.env/lib/python3.10/site-packages/trytond/modules/__init__.py", line 425, in load_modules
    _load_modules(update)
  File "/home/tryton/afx_project/.env/lib/python3.10/site-packages/trytond/modules/__init__.py", line 395, in _load_modules
    load_module_graph(graph, pool, update, lang, indexes)
  File "/home/tryton/afx_project/.env/lib/python3.10/site-packages/trytond/modules/__init__.py", line 221, in load_module_graph
    register_classes(classes, module)
  File "/home/tryton/afx_project/.env/lib/python3.10/site-packages/trytond/modules/__init__.py", line 181, in register_classes
    cls.__register__(module)
  File "/home/tryton/afx_project/.env/lib/python3.10/site-packages/trytond/model/modelsql.py", line 463, in __register__
    table = cls.__table_handler__(module_name)
  File "/home/tryton/afx_project/.env/lib/python3.10/site-packages/trytond/model/modelsql.py", line 447, in __table_handler__
    return backend.TableHandler(cls, history=history)
  File "/home/tryton/afx_project/.env/lib/python3.10/site-packages/trytond/backend/table.py", line 23, in __new__
    instance._init(model, history=history)
  File "/home/tryton/afx_project/.env/lib/python3.10/site-packages/trytond/backend/postgresql/table.py", line 44, in _init
    self.is_owner, = cursor.fetchone()
TypeError: cannot unpack non-iterable NoneType object

So I asked Qwen AI and I was told to implement TableHandlerInterface, so I did below:

class ProjectTrackListRecord(ModelSQL, ModelView):
    'Project Track List'
    __name__ = 'afx.project.tracklist.record'
    _table = 'afx_project_tracklist_record'  # Name of your actual DB view
    
    project_id = fields.Integer('Project ID')
    resource_id = fields.Char('Resource ID')
    resource_name = fields.Char('Resource Name')
    client_name = fields.Char('Client Name')
    pic = fields.Char('PIC')
    team = fields.Char('Team')
    proj_no = fields.Char('Project No')
    proj_name = fields.Char('Project Name')
    po_no = fields.Char('PO No')
    so_no = fields.Char('S/O No')
    # quote_no = fields.Char('Quote No')
    # loa = fields.Char('LOA')
    start_date = fields.Char('Start Date')
    end_date = fields.Char('End Date')
    # duration = fields.Char('Duration')
    # allocation = fields.Char('Allocaion')
    # status = fields.Char('Project Status')
    # completion = fields.Char('Completion')

    @classmethod
    def __setup__(cls):
        cls._readonly = True
        cls._history = False
        super(ProjectTrackListRecord, cls).__setup__()

    @classmethod
    def __table_handler__(cls, module_name=None, **kwargs):
        return ViewTableHandler(cls)

class ViewTableHandler(TableHandlerInterface):
    """
    A custom table handler for mapping Tryton models to read-only database views.
    Assumes all required columns and table exist in the database.
    """

    def _init(self, model, history=False):
        """
        Initialize the handler with the model.
        Skips ownership checks and DDL operations.
        """
        super()._init(model, history=history)
        self.exists = True           # Assume the view exists
        self.is_owner = False        # Not an owner (read-only)
        self.def_columns = []        # No column definitions to manage
        self.not_nulls = set()       # Views typically don't enforce NOT NULL
        self.uniques = {}            # Skip unique constraints
        self.indexes = {}            # Skip index management

    @classmethod
    def table_exist(cls, table_name):
        """
        Always return True since the view must already exist.
        """
        return True

    @classmethod
    def table_rename(cls, old_name, new_name):
        """
        Prevent renaming of views via Tryton.
        """
        pass

    def column_exist(self, column_name):
        """
        Assume all columns exist; useful for skipping field-related checks.
        """
        return True

    def alter_size(self, column_name, column_type):
        """
        Skip altering column size.
        """
        pass

    def alter_type(self, column_name, type_):
        """
        Skip changing column types.
        """
        pass

    def column_is_type(self, column_name, type_, *, size=-1):
        """
        Fake confirmation that the column matches the expected type.
        """
        return True

    def db_default(self, column_name, value):
        """
        Skip setting defaults since views cannot have defaults.
        """
        pass

    def add_column(self, column_name, abstract_type, default=None, comment=''):
        """
        Skip adding columns since views are fixed.
        """
        pass

    def add_fk(self, column_name, reference, on_delete=None):
        """
        Skip foreign key operations.
        """
        pass

    def drop_fk(self, column_name, table=None):
        """
        Skip FK removal.
        """
        pass

    def not_null_action(self, column_name, action='add'):
        """
        Skip NOT NULL constraint changes.
        """
        pass

    def add_constraint(self, ident, constraint):
        """
        Skip adding constraints.
        """
        pass

    def drop_constraint(self, ident, table=None):
        """
        Skip dropping constraints.
        """
        pass

    def create_index(self, index):
        """
        Skip creating indexes.
        """
        pass

    def drop_column(self, column_name):
        """
        Skip dropping columns.
        """
        pass

    @classmethod
    def drop_table(cls, model, table, cascade=False):
        """
        Skip table deletion.
        """
        pass

    def column_default(self, column_name):
        """
        Return None because views shouldn't have column defaults.
        """
        return None

    def index_action(self, columns, action='add', where=None):
        """
        Skip index actions.
        """
        pass

    def sequence_exist(self):
        """
        Views do not have sequences.
        """
        return False

    def sequence_create(self):
        """
        Skip sequence creation.
        """
        pass

    def sequence_update(self):
        """
        Skip updating sequences.
        """
        pass

    def sequence_rename(self, old_name):
        """
        Skip renaming sequences.
        """
        pass

    def drop_sequence(self):
        """
        Skip dropping sequences.
        """
        pass

And this made me passed the compile time, but now I got Runtime Error:

Traceback (most recent call last):
  File "/trytond/wsgi.py", line 97, in dispatch_request
    return endpoint(request, **request.view_args)
  File "/trytond/protocols/dispatcher.py", line 44, in rpc
    return methods.get(request.rpc_method, _dispatch)(
  File "/trytond/wsgi.py", line 77, in wrapper
    return func(request, *args, **kwargs)
  File "/trytond/protocols/wrappers.py", line 206, in wrapper
    result = func(request, pool, *args, **kwargs)
  File "/trytond/protocols/dispatcher.py", line 216, in _dispatch
    result = rpc.result(meth(*c_args, **c_kwargs))
  File "/trytond/model/modelsql.py", line 1219, in read
    getter_result = field.get(ids, cls, fname, values=result)
  File "/trytond/model/fields/one2many.py", line 170, in get
    targets.append([r.id for r in Target.search(clause, order=order)])
  File "/trytond/model/modelsql.py", line 1870, in search
    cursor.execute(*select)
  File "/trytond/backend/postgresql/database.py", line 78, in execute
    cursor.execute(self, sql, args)
psycopg2.errors.UndefinedColumn: column a.id does not exist
LINE 1: SELECT "a"."id" AS "id", "a"."client_name" AS "client_name",...
               ^

Bromo

This is not a compilation error but just that the table handler check who is the owner of the table.
But the PostgreSQL TableHandler is only testing against table and not view.

Do not trust AI.

We could add support for PostgreSQL VIEW, it would probably be better than the proposal of Draft: Allow to define materialized views (!1705) · Merge requests · Tryton / Tryton · GitLab which is only for materialized.

So for now view are not supported but indeed they are not that much different than a table_query so you need to define a unique id.

1 Like

Ok let me try using table_query() that combine tables.

Bromo

Hi @ced or anyone who may know,

I explore table_query(cls) method in the sample of aggregate model, but I couldn’t find any comprehensive tutorial on how to perform join query. Can anybody point me to any link (git maybe) for me to study more on building query?
Any advice would be appreciated.

Best regards
Bromo

See python-sql · PyPI

1 Like

Hi all,

Thanks to @ced for the link to learn on python sql.
So I create the code inside def table_query(cls) like below:

from trytond.model import ModelSQL, ModelView, fields
from trytond.pool import Pool
from sql.functions import RowNumber
from sql import *
from sql.aggregate import *
from sql.conditionals import *
import logging

logger = logging.getLogger(__name__)

class ProjectTrackListRecord(ModelSQL, ModelView):
    'Project Track List'
    __name__ = 'afx.project.tracklist.record'

    id = fields.Integer('ID', readonly=True)
    project_id = fields.Integer('Project ID')
    resource_id = fields.Char('Resource ID')
    resource_name = fields.Char('Resource Name')
    client_name = fields.Char('Client Name')
    pic = fields.Char('PIC')
    team = fields.Char('Team')
    proj_no = fields.Char('Project No')
    proj_name = fields.Char('Project Name')
    po_no = fields.Char('PO No')
    so_no = fields.Char('S/O No')
    start_date = fields.Char('Start Date')
    end_date = fields.Char('End Date')

    @classmethod
    def table_query(cls):
        pool = Pool()
        Project = pool.get('afx.project')
        ProjectMember = pool.get('afx.project.member')
        Employee = pool.get('afx.employee')
        Team = pool.get('afx.team')
        Company = pool.get('company.company')
        Party = pool.get('party.party')

        afx_project = Project.__table__()
        afx_project_member = ProjectMember.__table__()
        afx_employee = Employee.__table__()
        afx_team = Team.__table__()
        company_company = Company.__table__()
        party_party = Party.__table__()

        # Build nested RIGHT JOINs
        join1 = afx_project.join(afx_project_member)
        join1.condition = join1.right.afx_project_member.project == afx_project.id
        join2 = join1.join(afx_employee)
        join2.condition = join2.right.afx_employee.ref_employee_id == afx_project_member.member
        join3 = join2.join(afx_team)
        join3.condition = join3.right.afx_team.id == afx_employee.team
        join4 = join3.join(company_company)
        join4.condition = join4.right.company_company.id == afx_project.customer
        join = join4.join(party_party)
        join.condition = join.right.party_party.id == join4.right.company_company

        # Define columns with ROW_NUMBER()
        columns = [
            RowNumber().over(order_by=[afx_project.id]).as_('id'),
            afx_project.id.as_('project_id'),
            afx_employee.employee_id.as_('resource_id'),
            afx_employee.name.as_('resource_name'),
            party_party.name.as_('client_name'),
            afx_project.user.as_('pic'),
            afx_team.name.as_('team'),
            afx_project.proj_no.as_('proj_no'),
            afx_project.proj_name.as_('proj_name'),
            afx_project.po_no.as_('po_no'),
            afx_project.so_no.as_('so_no'),
            afx_project.start_date.as_('start_date'),
            afx_project.end_date.as_('end_date'),
        ]

        return join.select(*columns)

My table_query() code is as above, because my join select query (if presented in sql) would be like below:

SELECT 
    ROW_NUMBER() OVER (ORDER BY pro.id) AS id,  -- Auto-incremented ID
    pro.id AS project_id,
    emp.employee_id AS resource_id,
    emp.name AS resource_name,
    p_p.name AS client_name,
    pro.user AS pic,
    tim.name AS team,
    pro.proj_no AS proj_no,
    pro.proj_name AS proj_name,
    pro.po_no AS po_no, 
    pro.so_no AS so_no,
    pro.start_date AS start_date,
    pro.end_date AS end_date
FROM 
    afx_project pro
LEFT JOIN 
    afx_project_member mem ON pro.id = mem.project
LEFT JOIN 
    afx_employee emp ON mem.member = emp.ref_employee_id
LEFT JOIN
    afx_team tim ON emp.team = tim.id
LEFT JOIN
    company_company com ON pro.customer = com.id
LEFT JOIN
    party_party p_p ON com.party = p_p.id;

But when I call the above Aggregate model from below model:

class ProjectTrackList(ModelSQL, ModelView):
    'Project Track List'
    __name__ = 'afx.project.tracklist'
    
    name = fields.Char('Name', required=True)
	
	#---- many other fields -----

    records = fields.One2Many('afx.project.tracklist.record', 'project_id', 'Matrix', domain=[], states={
        'invisible': Eval('id', -1) < 0
    })

It throws error as below, as if there is no ‘project’ attribute on afx_project_member table, but actually it has.

Traceback (most recent call last):
  File "/home/tryton/afx_project/.env/lib/python3.10/site-packages/trytond/protocols/dispatcher.py", line 216, in _dispatch
    result = rpc.result(meth(*c_args, **c_kwargs))
  File "/home/tryton/afx_project/.env/lib/python3.10/site-packages/trytond/model/modelsql.py", line 1219, in read
    getter_result = field.get(ids, cls, fname, values=result)
  File "/home/tryton/afx_project/.env/lib/python3.10/site-packages/trytond/model/fields/one2many.py", line 170, in get
    targets.append([r.id for r in Target.search(clause, order=order)])
  File "/home/tryton/afx_project/.env/lib/python3.10/site-packages/trytond/model/modelsql.py", line 1823, in search
    tables, expression, union_orderings = cls.__search_query(
  File "/home/tryton/afx_project/.env/lib/python3.10/site-packages/trytond/model/modelsql.py", line 1769, in __search_query
    tables, expression = cls.search_domain(domain)
  File "/home/tryton/afx_project/.env/lib/python3.10/site-packages/trytond/model/modelsql.py", line 1916, in search_domain
    tables[None] = (cls.__table__(), None)
  File "/home/tryton/afx_project/.env/lib/python3.10/site-packages/trytond/model/modelsql.py", line 435, in __table__
    return cls.table_query()
  File "/home/tryton/afx_project/afx_project/project_tracklist_record.py", line 48, in table_query
    join1.condition = join1.right.afx_project_member.project == afx_project.id
AttributeError: 'Column' object has no attribute 'project'

Again, since I am not experienced in python.. I use AI but with no success.
If anyone can spot error on my code, please advice me

Thank you in advance.

Bromo

You do not need to define again id, it is already defined by inherited Model.

The join1.right is already the afx_project_member.
But you can write this simpler as you do not need to “introspect the join” like:

query = (afx_project
    .join(afx_project_member, condition=afx_project_member.project == afx_project.id)
    .join(...)
    ...)
1 Like

Ok my code now is like below, no more property error. Many thanks to @ced .
But since I want to be able to render the whole records, I set domain on search to be domain=[]

class ProjectTrackListRecord(ModelSQL, ModelView):
    'Project Track List'
    __name__ = 'afx.project.tracklist.record'

    project_id = fields.Integer('Project ID')
    resource_id = fields.Char('Resource ID')
    resource_name = fields.Char('Resource Name')
    client_name = fields.Char('Client Name')
    pic = fields.Char('PIC')
    team = fields.Char('Team')
    proj_no = fields.Char('Project No')
    proj_name = fields.Char('Project Name')
    po_no = fields.Char('PO No')
    so_no = fields.Char('S/O No')
    start_date = fields.Char('Start Date')
    end_date = fields.Char('End Date')

    @classmethod
    def table_query(cls):
        pool = Pool()
        Project = pool.get('afx.project')
        ProjectMember = pool.get('afx.project.member')
        Employee = pool.get('afx.employee')
        Team = pool.get('afx.team')
        Company = pool.get('company.company')
        Party = pool.get('party.party')

        afx_project = Project.__table__()
        afx_project_member = ProjectMember.__table__()
        afx_employee = Employee.__table__()
        afx_team = Team.__table__()
        company_company = Company.__table__()
        party_party = Party.__table__()

        row_number_window = Window(partition=[afx_project.id], order_by=[afx_project.id])

        # Build nested RIGHT JOINs
        join = (afx_project
                 .join(afx_project_member, condition=afx_project.id == afx_project_member.project)
                 .join(afx_employee, condition=afx_employee.ref_employee_id == afx_project_member.member)
                 .join(afx_team, condition=afx_team.id == afx_employee.team)
                 .join(company_company, condition=company_company.id == afx_employee.company)
                 .join(party_party, condition=party_party.id == company_company.party))
        
        # Define columns with ROW_NUMBER()
        columns = [
            RowNumber(window=row_number_window).as_('id'),
            afx_project.id.as_('project_id'),
            afx_employee.employee_id.as_('resource_id'),
            afx_employee.name.as_('resource_name'),
            party_party.name.as_('client_name'),
            afx_project.user.as_('pic'),
            afx_team.name.as_('team'),
            afx_project.proj_no.as_('proj_no'),
            afx_project.proj_name.as_('proj_name'),
            afx_project.po_no.as_('po_no'),
            afx_project.so_no.as_('so_no'),
            afx_project.start_date.as_('start_date'),
            afx_project.end_date.as_('end_date'),
        ]

        return join.select(*columns)
    
    @classmethod
    def search(cls, domain, offset=0, limit=None, order=None, count=False, query=False):
        domain = []
        return super().search(domain, offset, limit, order, count, query)
  

But this approach has triggered somekind of access denied like below:

Traceback (most recent call last):
  File "/trytond/wsgi.py", line 97, in dispatch_request
    return endpoint(request, **request.view_args)
  File "/trytond/protocols/dispatcher.py", line 44, in rpc
    return methods.get(request.rpc_method, _dispatch)(
  File "/trytond/wsgi.py", line 77, in wrapper
    return func(request, *args, **kwargs)
  File "/trytond/protocols/wrappers.py", line 206, in wrapper
    result = func(request, pool, *args, **kwargs)
  File "/trytond/protocols/dispatcher.py", line 216, in _dispatch
    result = rpc.result(meth(*c_args, **c_kwargs))
  File "/trytond/model/modelsql.py", line 1219, in read
    getter_result = field.get(ids, cls, fname, values=result)
  File "/trytond/model/fields/one2many.py", line 173, in get
    for t in Target.read(to_read, ['id', self.field])}
  File "/trytond/model/modelsql.py", line 1166, in read
    raise RuntimeError("Undetected access error")
RuntimeError: Undetected access error

How can I bypass this? since the reason I query all these records is for user to be able to monitor the entire on-going projects?

Regards
Bromo

Usually this is because the generated id is not unique nor stable.
I do not think you can achieve such property using RowNumber.

I see,

Ok thank you @ced , I think I will use real table and updates the data each time there is updates on the original table.

Regards
Bromo

It is hard to say without understanding the data model, but I think you just need to create a unique id out of the project and member. So you could use sql_pairing.

Hi @ced ,

So, regarding this issue.. I end up creating a table and it will be populate from the view object.
so I create a static method to initiate the table population as below:

    @staticmethod
    def populate_this_table(cls):
        tracklist_records_view = Table('afx_project_tracklist_view')
        select = tracklist_records_view.select()
        results = select.select()
        if results:
            logger.info(">>" * 40)
            logger.info(f"{results}")
            logger.info(">>" * 40)

With this method, I plan to query all records from the VIEW object and insert it into this class table. My question is, how to execute the select? I hope I can see some records in the results variable.. because my log on results only shows the SQL statement

[Tue May 13 08:54:32 2025] INFO:trytond.modules.afx_project.project_tracklist:>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>> [project_tracklist.py:150]
[Tue May 13 08:54:32 2025] INFO:trytond.modules.afx_project.project_tracklist:SELECT * FROM (SELECT * FROM "afx_project_tracklist_view" AS "b") AS "a" [project_tracklist.py:151]
[Tue May 13 08:54:32 2025] INFO:trytond.modules.afx_project.project_tracklist:>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>> [project_tracklist.py:152]

Can u help me @ced or anybody who understand?
I am really sorry if my understanding in python is very limited.

regards,
Bromo

Well that is not a recommended way to create reports especially when there is such clear SQL query to rely on.

If you want to execute a query, you must execute it using a cursor from the Transaction.connection.

1 Like

Reading your first post, I immediately thought of a context form. Take for example a look at Financial → Reporting → Income Statement. In the form part you can select different options and then refresh the list of data.

Take a look at modules/account/account.py · d7ef36068a6aa0ca642e5de2b02615aa0f3b856f · Tryton / Tryton · GitLab how this is coded and try things out.

And you probably don’t need to create a new database table, so you can leave out the ModelSQL. Using the ModelView only should work too. Define some fields and make sure you have the table_query function to get the data from the database and populate the fields.

1 Like