cartodb: how to display data from a related table in the info window?

Question

I have a table of buildings. I have a table of all the things in each building. It is a one to many relationship. How can i make info windows which display a list of the things in the building?

Answer 1

You can find an example here: https://team.cartodb.com/u/iriberri/viz/b19f4074-d467-11e4-8878-0e853d047bba/public_map

What you need is: * A table with the buildings that includes their location (geometry). I'm using "house" * A table with the "things". I'm using "rooms" * A common value between them, in my example below, both tables share the "name" column.

You'd need to use a query like the following one and leave it applied in your map:

SELECT  a.name, a.description, string_agg(b.name, ', ') as contents,a.the_geom_webmercator, Min(a.cartodb_id) cartodb_id
FROM houses a, rooms b where b.house = a.name
group by a.name, a.description, a.the_geom_webmercator

You can find the tables here:

Houses: https://team.cartodb.com/u/iriberri/tables/houses

Rooms: https://team.cartodb.com/u/iriberri/tables/rooms