Odoo中reporting_period_id字段点击触发psycopg2类型转换错误

我希望reporting_period_id字段仅显示所选特定财年内、与output_indicator_id匹配的报告期，但点击该字段时触发了数据库类型错误，以下是相关代码与错误信息：

模型代码 #

class ProgrammeLinking(models.Model):
    _name = 'mis.programme.linking'
    _description = 'linking and allocations'
    _rec_name = "financial_year_id"

    financial_year_id = fields.Many2one('res.financial.year',string='Financial Year')

    output_indicator_id = fields.Many2one('mis.output.indicator',string='APP Output indicator', store=True)
    allowed_output_indicator_ids = fields.Many2many(
        'mis.output.indicator', 
        compute="_compute_allowed_output_indicator_ids"
    )
    reporting_period_id = fields.Many2one('mis.create.link.national.app', string='Reporting Period')
    allowed_reporting_period_ids = fields.Many2many(
        'mis.create.link.national.app',
        compute="_compute_allowed_reporting_period_ids"
    )


    def _get_current_fy_programme_linking(self):
        for rec in self:
            return self.env['mis.create.link.national.app'].search([
                ('financial_year_id','=', rec.financial_year_id.id)
            ])


    @api.depends('outcome_id')
    def _compute_allowed_output_indicator_ids(self):
        for rec in self:
            if rec.outcome_id:
                fy_linkings = self._get_current_fy_programme_linking()
                output = fy_linkings \
                    .filtered(lambda l: l.outcome_id.id == rec.outcome_id.id) \
                    .mapped('output_indicator_id')
                _logger.info(f"Filtered ou {output}")
                rec.allowed_output_indicator_ids = output
            else:
                rec.allowed_output_indicator_ids = False

    
    @api.depends('output_indicator_id')
    def _compute_allowed_reporting_period_ids(self):
        for rec in self:
            if rec.output_indicator_id:
                fy_linkings = self._get_current_fy_programme_linking()
                period = fy_linkings \
                    .filtered(lambda l: l.output_indicator_id.id == rec.output_indicator_id.id) \
                    .mapped('reporting_period')
                _logger.info(f"Filtered periods {period}")
                rec.allowed_reporting_period_ids = period
            else:
                rec.allowed_reporting_period_ids = False

视图代码 #

<field name="allowed_output_indicator_ids" invisible="1"/>
<field name="output_indicator_id" required="1" options="{'no_create': True, 'no_open': True}" domain="[('id','in', allowed_output_indicator_ids)]"/>

<field name="allowed_reporting_period_ids" invisible="1"/>
<field name="reporting_period_id" required="1" options="{'no_create': True, 'no_open': True}" domain="[('id','in', allowed_reporting_period_ids)]"/>

错误信息 #

Traceback (most recent call last):
  File "C:\odoo14\server\odoo\http.py", line 639, in _handle_exception
    return super(JsonRequest, self)._handle_exception(exception)
  File "C:\odoo14\server\odoo\http.py", line 315, in _handle_exception
    raise exception.with_traceback(None) from new_cause
psycopg2.errors.InvalidTextRepresentation: invalid input syntax for type integer: "quarterly"
LINE 1: ...p" WHERE ("mis_create_link_national_app"."id" in ('quarterly...
                                                             ^

关键说明 #

mis.create.link.national.app模型中的reporting_period是选择字段，当前财年内该字段值为"quarterly"。

问题根源 #

allowed_reporting_period_ids是mis.create.link.national.app的多对多字段，本应存储该模型的记录ID，但当前代码用.mapped('reporting_period')提取的是选择字段的字符串值（如"quarterly"），而非记录ID。视图中[('id','in', allowed_reporting_period_ids)]的筛选逻辑，会把字符串当成ID传入数据库，导致PostgreSQL尝试将字符串转为整数类型时失败。

修复方案 #

修改_compute_allowed_reporting_period_ids方法，直接映射符合条件的mis.create.link.national.app记录，而非其reporting_period字段值：

@api.depends('output_indicator_id')
def _compute_allowed_reporting_period_ids(self):
    for rec in self:
        if rec.output_indicator_id:
            fy_linkings = self._get_current_fy_programme_linking()
            # 直接筛选符合条件的记录，而非提取字段值
            period_records = fy_linkings.filtered(
                lambda l: l.output_indicator_id.id == rec.output_indicator_id.id
            )
            _logger.info(f"Filtered period records {period_records}")
            rec.allowed_reporting_period_ids = period_records
        else:
            rec.allowed_reporting_period_ids = False

额外优化点 #

_get_current_fy_programme_linking方法存在逻辑问题：循环内直接return会只处理第一条记录，建议重构为批量查询：

def _get_current_fy_programme_linking(self):
    # 批量查询所有关联财年的记录，避免循环内重复查询
    fy_ids = self.mapped('financial_year_id.id')
    return self.env['mis.create.link.national.app'].search([
        ('financial_year_id', 'in', fy_ids)
    ])

内容的提问来源于stack exchange，提问作者MarkHenri