使用原始SQL查询Odoo多对多字段

I want to display many-to-many field in report.

我想在报告中显示多对多字段。

Currently my model is as follows:

目前我的模型如下：

from openerp.esv import orm, fields


class myClass(orm.Model):
    _name = 'my.Class' 
    _columns = {
        'teacher_id': fields.many2many('fci.staff','lgna_teacher','ids_lol',
                                       'teacher_ids','Observers'),
    }

And I want to display them using SQL select statement.

我想使用SQL select语句显示它们。

1 个解决方案

#1

In my example below, I'm considering that teachers and courses have a many-to-many relationship: teachers can teach multiple courses and courses can be taught by multiple teachers.

在下面的例子中，我认为教师和课程有多对多的关系：教师可以教多门课程，课程可以由多位老师教授。

from openerp.osv import orm, fields

class Teachers(orm.Model):
    _name = 'teachers'

    name = fields.Char() 

class Course(rm.Model):
    _name = 'course'

    title = fields.Char()
    teacher_ids = fields.Many2many('teachers', 'teacher_course_rel', 'course_id',
                                  'teacher_id', string='Teachers')

Using SQL (either using Odoo API or your DBMS), you can query the junction table (or cross-reference table) teacher_course_rel to retrieve the needed columns from each table.

使用SQL（使用Odoo API或DBMS），您可以查询联结表（或交叉引用表）teacher_course_rel以从每个表中检索所需的列。

For instance, the query below retrieves all the teachers' names teaching the Physics course:

例如，下面的查询检索教授物理课程的所有教师姓名：

SELECT c.title, t.name
FROM teacher AS t
INNER JOIN teacher_course_rel AS tcr
    ON t.id = tcr.teacher_id
INNER JOIN course AS c
    ON tcr.course_id = c.id
WHERE c.title = 'Physics'

Please note that I have used an SQL INNER JOIN which returns rows from the two tables only when the conditions are met (i.e. the two INNER JOIN conditions and the WHERE condition). For your purposes, you may wish to use a different type of join depending on the information you wish to retrieve from both your tables.

请注意，我使用了SQL INNER JOIN，它只在满足条件时返回两个表中的行（即两个INNER JOIN条件和WHERE条件）。出于您的目的，您可能希望使用不同类型的连接，具体取决于您希望从两个表中检索的信息。

#1