Sails Waterline SQL join in populate

Question

Does Waterline have any support for performing SQL joins (aside from query())? I've set up the associations in my models, but the actual SQL queries that are generated are looping select statements. Is there currently only support for "n+1 selects"? Am I missing a configuration option somewhere?

Here is an example of a 1->Many populate that is looping select statements: (sails/waterline/sails-mysql v0.10.0-rc8)

/**
* City.js
*/
module.exports = {
"adapter": "someMysqlServer",
"tableName": "city",
autoCreatedAt: false,
autoUpdatedAt: false,
schema: true,
attributes: {
    "id": {
        "type": "integer",
        "primaryKey": true,
        "autoIncrement": true,
        "columnName": "ID"
    },
    "Name": {
        "type": "string",
        "maxLength": 35
    },
    "District": {
        "type": "string",
        "maxLength": 20
    },
    "Population": {
        "type": "integer"
    },
    "CountryCode": {
        //a city has one country
        "model": "Country"
    }
}};




/**
* Country.js
*/

module.exports = {
  "adapter": "someMysqlServer",
  "tableName": "country",
  autoCreatedAt: false,
  autoUpdatedAt: false,
  schema: true,
  attributes: {
      "id": {
          "type": "string",
          "required": true,
          columnName: "Code",
          "primaryKey": true,
          "maxLength": 3
      },
      "Name": {
          "type": "string",
          "maxLength": 52
      },
      Continent: "string",
      "Region": {
          "type": "string",
          "maxLength": 26
      },
      SurfaceArea: "float",
      IndepYear: "integer",
      Population: "integer",
      LifeExpectancy: "float",
      GNP: "float",
      GNPOld: "float",
      LocalName: "string",
      GovernmentForm: "string",
      HeadOfState: "string",
      Capital: "integer",
      Code2: "string",
      Cities: {
          //country has many cities
          "collection": "City",
          "via": "CountryCode"
      }
  }
};

Then when I navigate to http://localhost:1337/country?populate=[Cities] my logs show the following sql was executed:

SELECT `country`.`Name`, `country`.`Continent`, `country`.`Region`, `country`.`SurfaceArea`, `country`.`IndepYear`, `country`.`Population`, `country`.`LifeExpectancy`, `country`.`GNP`, `country`.`GNPOld`, `country`.`LocalName`, `country`.`GovernmentForm`, `country`.`HeadOfState`, `country`.`Capital`, `country`.`Code2`,`country`.`Code` FROM `country` LIMIT 30 OFFSET 0
SELECT `city`.`Name`, `city`.`CountryCode`, `city`.`District`, `city`.`Population`, `city`.`ID` FROM `city` WHERE `city`.`CountryCode`='ABW' LIMIT 30
SELECT `city`.`Name`, `city`.`CountryCode`, `city`.`District`, `city`.`Population`, `city`.`ID` FROM `city` WHERE `city`.`CountryCode`='AFG' LIMIT 30
SELECT `city`.`Name`, `city`.`CountryCode`, `city`.`District`, `city`.`Population`, `city`.`ID` FROM `city` WHERE `city`.`CountryCode`='AGO' LIMIT 30
SELECT `city`.`Name`, `city`.`CountryCode`, `city`.`District`, `city`.`Population`, `city`.`ID` FROM `city` WHERE `city`.`CountryCode`='AIA' LIMIT 30
SELECT `city`.`Name`, `city`.`CountryCode`, `city`.`District`, `city`.`Population`, `city`.`ID` FROM `city` WHERE `city`.`CountryCode`='ALB' LIMIT 30
SELECT `city`.`Name`, `city`.`CountryCode`, `city`.`District`, `city`.`Population`, `city`.`ID` FROM `city` WHERE `city`.`CountryCode`='AND' LIMIT 30
SELECT `city`.`Name`, `city`.`CountryCode`, `city`.`District`, `city`.`Population`, `city`.`ID` FROM `city` WHERE `city`.`CountryCode`='ANT' LIMIT 30
SELECT `city`.`Name`, `city`.`CountryCode`, `city`.`District`, `city`.`Population`, `city`.`ID` FROM `city` WHERE `city`.`CountryCode`='ARE' LIMIT 30
SELECT `city`.`Name`, `city`.`CountryCode`, `city`.`District`, `city`.`Population`, `city`.`ID` FROM `city` WHERE `city`.`CountryCode`='ARG' LIMIT 30
SELECT `city`.`Name`, `city`.`CountryCode`, `city`.`District`, `city`.`Population`, `city`.`ID` FROM `city` WHERE `city`.`CountryCode`='ARM' LIMIT 30
SELECT `city`.`Name`, `city`.`CountryCode`, `city`.`District`, `city`.`Population`, `city`.`ID` FROM `city` WHERE `city`.`CountryCode`='ASM' LIMIT 30
SELECT `city`.`Name`, `city`.`CountryCode`, `city`.`District`, `city`.`Population`, `city`.`ID` FROM `city` WHERE `city`.`CountryCode`='ATA' LIMIT 30
SELECT `city`.`Name`, `city`.`CountryCode`, `city`.`District`, `city`.`Population`, `city`.`ID` FROM `city` WHERE `city`.`CountryCode`='ATF' LIMIT 30
SELECT `city`.`Name`, `city`.`CountryCode`, `city`.`District`, `city`.`Population`, `city`.`ID` FROM `city` WHERE `city`.`CountryCode`='ATG' LIMIT 30
SELECT `city`.`Name`, `city`.`CountryCode`, `city`.`District`, `city`.`Population`, `city`.`ID` FROM `city` WHERE `city`.`CountryCode`='AUS' LIMIT 30
SELECT `city`.`Name`, `city`.`CountryCode`, `city`.`District`, `city`.`Population`, `city`.`ID` FROM `city` WHERE `city`.`CountryCode`='AUT' LIMIT 30
SELECT `city`.`Name`, `city`.`CountryCode`, `city`.`District`, `city`.`Population`, `city`.`ID` FROM `city` WHERE `city`.`CountryCode`='AZE' LIMIT 30
SELECT `city`.`Name`, `city`.`CountryCode`, `city`.`District`, `city`.`Population`, `city`.`ID` FROM `city` WHERE `city`.`CountryCode`='BDI' LIMIT 30
SELECT `city`.`Name`, `city`.`CountryCode`, `city`.`District`, `city`.`Population`, `city`.`ID` FROM `city` WHERE `city`.`CountryCode`='BEL' LIMIT 30
SELECT `city`.`Name`, `city`.`CountryCode`, `city`.`District`, `city`.`Population`, `city`.`ID` FROM `city` WHERE `city`.`CountryCode`='BEN' LIMIT 30
SELECT `city`.`Name`, `city`.`CountryCode`, `city`.`District`, `city`.`Population`, `city`.`ID` FROM `city` WHERE `city`.`CountryCode`='BFA' LIMIT 30
SELECT `city`.`Name`, `city`.`CountryCode`, `city`.`District`, `city`.`Population`, `city`.`ID` FROM `city` WHERE `city`.`CountryCode`='BGD' LIMIT 30
SELECT `city`.`Name`, `city`.`CountryCode`, `city`.`District`, `city`.`Population`, `city`.`ID` FROM `city` WHERE `city`.`CountryCode`='BGR' LIMIT 30
SELECT `city`.`Name`, `city`.`CountryCode`, `city`.`District`, `city`.`Population`, `city`.`ID` FROM `city` WHERE `city`.`CountryCode`='BHR' LIMIT 30
SELECT `city`.`Name`, `city`.`CountryCode`, `city`.`District`, `city`.`Population`, `city`.`ID` FROM `city` WHERE `city`.`CountryCode`='BHS' LIMIT 30
SELECT `city`.`Name`, `city`.`CountryCode`, `city`.`District`, `city`.`Population`, `city`.`ID` FROM `city` WHERE `city`.`CountryCode`='BIH' LIMIT 30
SELECT `city`.`Name`, `city`.`CountryCode`, `city`.`District`, `city`.`Population`, `city`.`ID` FROM `city` WHERE `city`.`CountryCode`='BLR' LIMIT 30
SELECT `city`.`Name`, `city`.`CountryCode`, `city`.`District`, `city`.`Population`, `city`.`ID` FROM `city` WHERE `city`.`CountryCode`='BLZ' LIMIT 30
SELECT `city`.`Name`, `city`.`CountryCode`, `city`.`District`, `city`.`Population`, `city`.`ID` FROM `city` WHERE `city`.`CountryCode`='BMU' LIMIT 30
SELECT `city`.`Name`, `city`.`CountryCode`, `city`.`District`, `city`.`Population`, `city`.`ID` FROM `city` WHERE `city`.`CountryCode`='BOL' LIMIT 30

I'm getting the same problem. Testing if I use the ?populate=[model] url I get the same behavior. If I explicitly call Model.find().populate('OtherModel') I get a slightly more optimized version (using IN) but still no join. — Rowan, Jul 18 '14 at 10:50

score 1 · Answer 1 · answered Jul 26 '14 at 22:54

1

The .populate() implementation in beta is deliberately simplified to support the broadest possible set of adapters. In the final v0.10 release, the supported "core" SQL adapters (sails-mysql and sails-postgresql) will do actual joins.

answered Jul 26 '14 at 22:54

sgress454

24,870
4
74
92

@sgress454Any update on this? I just installed sails 0.10.2 and sails-mysql 0.10.4 and mysql logs show the following output: `(SELECT * FROM city AS city WHERE CountryCode = "ABW" ORDER BY city.id ASC LIMIT 30) UNION (SELECT * FROM city AS city WHERE CountryCode = "AFG" ORDER BY city.id ASC LIMIT 30) UNION (SELECT * FROM city AS city WHERE CountryCode = "AGO" ORDER BY city.id ASC LIMIT 30) (...)` – jaredfromsubway Aug 06 '14 at 22:10
This is because you're hitting the blueprint route which has a default limit of 30 records. You can't do a simple join in this case because the query would then return only 30 rows, where what you really want is up to 900 rows (30 countries, each with 30 cities). Hence the `UNION`, which is still way better than doing one separate query for each country. If you can boil this down to a single query, we'd be very interested in seeing it! See [this article](http://www.xaprb.com/blog/2006/12/07/how-to-select-the-firstleastmax-row-per-group-in-sql/) for more info on the problem. – sgress454 Aug 06 '14 at 22:38

Sails Waterline SQL join in populate

1 Answers1