1

Basically we have this concept:

Main table:

vehicles

id|name|created_by|type|created_at

vehicle field data

truck_fields

vehicle_id|door_count|height|max_load|truck_size

car_fields

vehicle_id|doors|max_passengers|engine_size

motorbike_fields

vehicle_id|wheel_count|max_weight

aero_plane_fields

vehicle_id|window_count|engine_power

aero_plane_fields

vehicle_id|window_count|engine_power

etc....

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.

so e.g.

SELECT v.*, row_to_json(d) from vehicles JOIN truck_fields d on d.vehicle_id = v.id
UNION ALL
SELECT v.*, row_to_json(d) from vehicles JOIN car_fields d on d.vehicle_id = v.id
UNION ALL
SELECT v.*, row_to_json(d) from vehicles JOIN motorbike_fields d on d.vehicle_id = v.id
UNION ALL
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.

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Browse other questions tagged or ask your own question.