0

I am trying to display the results of the following:

cartodb.createLayer(map, {
user_name: "tobagoborn",
type: 'cartodb',
sublayers: [{
    sql: "(SELECT L.the_geom_webmercator, L.landing_sites, F.first_name, F.last_name,C.number_of_species, C.cartodb_id FROM catch C, lsites L, fishermen F WHERE C.have_catch = 1 AND C.site_code = L.site_code AND C.fisherman_id = F.fisherman_id;}", // AND C.catch_date > now()::date";
    cartocss: '#catch {marker-fill: #FF0000;}',
    interactivity: 'landing_sites,first_name,last_name,number_of_species',
    }]
})
.addTo(map)
.on('done', function(layer) {
    var sublayer0 = layer.getSubLayer(0);
    cdb.vis.Vis.addInfowindow(map, sublayer0, 
       ['landing_sites','first_name','last_name','number_of_species'],
       {infowindowTemplate: $('#infowindow_template').html()});
})
.on('error', function(err) {
    console.log(err);
   //log the error
});

I understand how to display the results of a single table query. but need some assistance with doing the same with the SQL statement above.

2

Your SQL is invalid. (The braces at beginning and end of statement).

"(SELECT L.the_geom_webmercator, L.landing_sites, F.first_name, F.last_name,C.number_of_species, C.cartodb_id FROM catch C, lsites L, fishermen F WHERE C.have_catch = 1 AND C.site_code = L.site_code AND C.fisherman_id = F.fisherman_id;}"

Should be

"SELECT L.the_geom_webmercator, L.landing_sites, F.first_name, F.last_name,C.number_of_species, C.cartodb_id FROM catch C, lsites L, fishermen F WHERE C.have_catch = 1 AND C.site_code = L.site_code AND C.fisherman_id = F.fisherman_id"
  • Thanks Jags!! It was the extra curly brace inside of the quotes of the SQL statement. Just a note... I did not get an error msg in my console – ChrisJ Jan 18 '16 at 22:51
  • @ChrisJ Yeah the error messages from layer creation calls can be vague AND/OR absent, so it's best to test things in the CartoDB Editor first. – Jags Jan 18 '16 at 23:57

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.