Table join not displaying in createLayer function in cartodb.js

Question

I am trying to build on the cartodb tutorial to develop a map of fibre networks in Africa. The example works fine when my SQL select is very simple i.e.

            var layerSource = {
                    user_name: 'afterfibre',
                    type: 'cartodb',
                    sublayers: [{
                        sql: "SELECT * FROM af_fibrephase",
                        cartocss: $("#category").text()
                    }]
                }

But when I attempt a more sophisticated query with a table join, such as below, the layer no longer renders.

            var layerSource = {
                    user_name: 'afterfibre',
                    type: 'cartodb',
                    sublayers: [{
                        sql: "SELECT af_fibrephase.the_geom, af_fibrephase.live, af_organisation.name FROM af_fibrephase, af_organisation WHERE af_fibrephase.operator_id = af_organisation.organisation_id",
                        cartocss: $("#category").text()
                    }]
                }

The SQL query tests ok via the API https://afterfibre.cartodb.com/api/v2/sql?q=SELECT%20af_fibrephase.the_geom,%20af_fibrephase.live,%20af_organisation.name%20FROM%20af_fibrephase,%20af_organisation%20WHERE%20af_fibrephase.operator_id%20=%20af_organisation.organisation_id

I sense I am doing something obvious wrong but can't work out what.

Answer 1

not sure if this is really an answer, but hopefully points you in the right direction.

I tried plugging it into CartoDB and got an error:

the_geom_webmercator column should be selected

I glanced through this, it might help http://docs.cartodb.com/tutorials/projections.html.

Changing the_geom to the_gem_webmercator worked for me. I also had to play around with the cartocss a little bit.

var layerSource = {
                user_name: 'afterfibre',
                type: 'cartodb',
                sublayers: [{
                    sql: "SELECT af_fibrephase.the_geom_webmercator, af_fibrephase.live, af_organisation.name FROM af_fibrephase, af_organisation WHERE af_fibrephase.operator_id = af_organisation.organisation_id",
                    cartocss: '#af_fibrephase { line-color: #C00;line-width: 1;}',
                }]
            }

Hopefully that helps