Basically we have this concept:
vehicle field data
Basically this is a contrived example which demonstrates our structure. We have a table of vehicles and n number of
*_fields tables where we store properties about each of these vehicles based on the type. We know these field tables and type mappings at dev time but are struggling in a way to write a query that would allows us to essentially lets say, paginate the list of vehicles WITH each rows associated fields.
SELECT v.*, row_to_json(d) from vehicles JOIN truck_fields d on d.vehicle_id = v.id
SELECT v.*, row_to_json(d) from vehicles JOIN car_fields d on d.vehicle_id = v.id
SELECT v.*, row_to_json(d) from vehicles JOIN motorbike_fields d on d.vehicle_id = v.id
SELECT v.*, row_to_json(d) from vehicles JOIN areoplane_fields d on d.vehicle_id = v.id
LIMIT 100 OFFSET 10
This is obviously extremely inefficient query so looking for help in a potentially either)
a. alternative structure that would better facilitate this query
b. a better query that would achieve this same outcome
We've tried alternatives like EAV structure but obviously this is quite a big anti-patten.