beriliox beriliox - 3 months ago 15
Python Question

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

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: enter image description here

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

IMAGE 2: enter image description here

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: enter image description here

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

Answer

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.