Reloading attributes of selected item using GeoServer/OpenLayers

Question

I have a PostGIS database with items whose geom is an ST_Point, so they show as a clickable point when shown in GeoServer/OpenLayers. Each of them has around 90 attributes and the database is normalized so that most columns don't contain the value that should actually be displayed but just the id of the text in another table.

To speed up the query, I'd prefer to only fetch the rows with the geoms that fit the current view as they are, so with the ids instead of the actual value but when someone clicks on a point, the values for that specific point should be queried and displayed.

So as an example, I'd have these rows (just 5 attribs and geom omitted):

    id | attrib1 | attrib2 | attrib3 | attrib4 | attrib5 
     1 |       1 |       3 |       1 |       5 |       3
     2 |       1 |       2 |       7 |       4 |       2
     3 |       2 |       3 |       4 |       3 |       1 
     ...

and they should be fetched as such to have a fast query but if a user clicks on the point "1", he should be shown e.g.

     1 |   Ford  | Mustang |    blue |    2017 |    used

instead of

     1 |       1 |       3 |       1 |       5 |       3

Is there a way to trigger a query for this row alone and to then display these values instead of the originally fetched row?

Answer 1

With correct indexing it should be quick enough with a simple view doing all the look ups before you produce the map. That is also the easy way to solve this problem.

However, if you really must do this then I would create two layers one on the single table for drawing and one on the combined view to use in getFeatureInfo requests.