odoo domain search "id in ids"

I have a model B with a Many2many field referencing model A. Now given an id of model A, I try to get the records of B that reference it.

Is this possible with Odoo search domains? Is it possible doing some SQL query?

Example

class A(models.Model):
    _name='module.a'

class B(models.Model):
    _name='module.b'
    a_ids = fields.Many2many('m.a')

I try to do something like

a_id = 5
filtered_b_ids = self.env['module.b'].search([(a_id,'in','a_ids')])

However, this is not a valid search in Odoo. Is there a way to let the database do the search? So far I fetch all records of B from the database and filter them afterward:

all_b_ids = self.env['module.b'].search([])
filtered_b_ids = [b_id for b_id in b_ids if a_id in b_id.a_ids]

However, I want to avoid fetching not needed records and would like to let the database do the filtering.

Solution

You should create the equivalent Many2many field in A.

class A(models.Model):
    _name='module.a'
    b_ids = fields.Many2many('module.b', 'rel_a_b', 'a_id', 'b_id')


class B(models.Model):
    _name='module.b'
    a_ids = fields.Many2many('module.a', 'rel_a_b', 'b_id', 'a_id')

In the field definition, the second argument is the name of the association table, and the two next ones are the name of the columns referencing the records of the two models. It's explained in the official ORM documentation. Then you just have to do my_a_record.b_ids.

If you prefer doing an SQL request because you don't want to add a python field to A, you can do so by calling self.env.cr.execute("select id from module_b b, ...").fetchall(). In your request you have to join the association table (so you need to specify a name for it and its columns, as described in my code extract, otherwise they are automatically named by Odoo and I don't know the rule).

I think it's still possible to use search domains without the field in A but it's tricky. You can try search([('a_ids','in', [a_id])]) but I'm really not sure.