Feeding the frontend – generating data for D3.js Part 2a
Ok so this is a bolt-on post to my previous post, where I am now essentially trying to figure out a nice way to map the data between the computers and their positions in a room. The way I’ve designed the system is that I have three tables with the relevant data:
- Hosts: The main piece of data here is the hostname and other data – but it also contains the room that the host belongs to (and the “place”, which is just a notional number that I placed on a diagram)
- Rooms: This is the list of rooms and their “layout type”. We could have ten different rooms each with the same layout, so this is a way to say which room has which layout type. Its sole purpose is to join the other two tables.
- Places: The combination of a layout type and a position will give you an x and a y coordinate. This is a relative coordinate. It doesnt have to know which rooms are associated with it, its purely data on where – for a given layout type – a “position” would exist.
The idea for this is that you would fetch the respective coordinates for a host from the places table. The hosts table only has the place, so its necessary to fetch the correct layout type that matches the room, and then fetch the coordinates based on that data.
Complicated? A little. Especially for something that doesn’t seem too big. But I want it to be scalable and to separate out the data for positions from the hosts; a room may entirely change its layout but, so long as the room doesn’t change, the only thing I would need to change is the layout type and then the coordinates. However, I could have just placed all of this data into the hosts table – or removed the third table for places and just have mapped each room directly to sets of coordinates (and I still may!).
But I did find a solution, although it took a few iterations. I needed to refresh myself on inner and left joins a bit but my original plan was to do a left join between the hosts table and places table where the hosts’ room is a specific room. But then the places table works on layout type and noot a room number.
Ok so the first step would be something like this:
SELECT hostname, place, room, posx, posy FROM hosts INNER JOIN places ON places.position = hosts.place WHERE room = "W004"
Select the hostname from hosts, the coordinates from places, the room and the place from the inner join’d josts and places. The inner join will be done where the position field from places matches the place field from hosts. To limit it, I then just put “where room = w004”.
This would return a lot of results, since there’s many hosts potentially with the same place and many places with the same position. The “where” limitation narrows the hosts down to only the selection relevant to the one room, but it still leaves lots of entries potentially from the position, since there will likely be n times as many results as there are positions and layouts. That would then give duplicate host results, a duplicate for each recurring position that appears in the places table.
So the next step is to narrow this further, which was causing me a lot of headaches. I had an idea to expand the “WHERE” statement to include the room, but that isn’t a part of the dataset constructed by the joins. The solution was to do two joins – the second join being where the layout type matches the room specified in the room table and where the room matches in both the room and hosts table. This narrows both the hosts and rooms down to just one room, which means in turn there will only be one layout – and this further restricts the results from places down to what I need.
SELECT hostname, place, rooms.room, posx, posy FROM hosts INNER JOIN places ON places.position = hosts.place INNER JOIN rooms ON rooms.room = hosts.room AND rooms.layoutType = places.layout WHERE rooms.room = "W004"
Note that I could have used left joins, which would preserve all the host data, but I am trying to narrow it down and there’s just no need for anything else. I should probably also tidy up some of the names a bit, especially since there is now ambiguity between the different “room”s, but not between place and position.
With that done, I can now replace my original SQL statement:
$getHosts = "SELECT * from hosts WHERE Room = '". $room ."'";
With the following:
$getHosts = "SELECT hostname, place, rooms.room, posx, posy FROM hosts INNER JOIN places ON hosts.place = places.position INNER JOIN rooms ON rooms.room = hosts.room AND rooms.layoutType = places.layout WHERE rooms.room = '". $room ."'";
A lot longer, but it returns a single dataset with all the position data we need for a given room!
‹ Feeding the frontend – displaying data with D3.js Part 2