How to retrieve the value of a many2many field for each sale order?

Question

I've created a many2many field in the sale.order model that it's related with fleet.vehicle model (id).

How it's showed in the follow image:

IMAGE 1:

Well, the 'x_vehiculo' field is a multi-selection field, How it's showed in the follow image:

IMAGE 2:

The idea is that for each sale order i can to store more that 1 vehicle.

The trouble is that i can't see the value for of x_vehiculo' field for each sale order when i make a query in the SGB Postgresql (PgAdmin III).

IMAGE 3:

There is any way to can retrieve values of 'x_vehicle' multi-selection field for each sale.order?

Please if somebody could help me. I'd be very gratefull.

Thanks you so much

score 5 · Accepted Answer · answered Aug 21 '16 at 22:07

The relation is not stored in the sale_order table. Since it's many2many, it is stored in a relational table. Based on your screenshot, your relational table is this one: x_fleet_vehicle_sale_order_rel

To query for the vehicles, you have to join through this table like so:

select so.name, fv.name
from sale_order so
left join x_fleet_vehicle_sale_order_rel rel on (rel.sale_order_id = so.id)
left join fleet_vehicle fv on (fv.id = rel.fleet_vehicle_id);

This will give you a row for each combination of sale.order and fleet.vehicle. If you want to group them by sale order you can do this:

select so.name, array_agg(fv.name) as vehicles
from sale_order so
left join x_fleet_vehicle_sale_order_rel rel on (rel.sale_order_id = so.id)
left join fleet_vehicle fv on (fv.id = rel.fleet_vehicle_id)
group by so.name;

This will return one row for each sale.order with a list of fleet.vehicle names attached to that order.

Thanks so much i can understand it and do it with your support. — beriliox, Aug 22 '16 at 01:35

score 1 · Answer 2 · answered Aug 21 '16 at 22:06

The field isn't going to be available on the table you declared it, it's a Many2many relationship so a separate 'join' table is going to be created for it, the name of that table is

x_fleet_vehicle_sale_order_rel

That table will contain the fields sale_order_id and fleet_vehicle_id

So you should be querying that table instead of the table where you declared x_vehiculo

How to retrieve the value of a many2many field for each sale order?

2 Answers2