logo标签列表

Sails Waterline中populate的SQL联接

node.jssails.jswaterline
5

除了query()外，Waterline是否支持执行SQL连接？我已经在我的模型中设置了关联，但实际生成的SQL查询是循环选择语句。当前是否只支持“n+1 selects”？我是否错过了某个配置选项？

这里是一个循环选择语句的1->Many populate示例： （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"
      }
  }
};

当我导航至 http://localhost:1337/country?populate=[Cities] 时，我的日志显示执行了以下SQL：

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
我遇到了同样的问题。测试使用 ?populate=[model] 的 URL 时，我得到了相同的行为。如果我显式调用 Model.find().populate('OtherModel')，我会得到一个稍微优化一些的版本（使用 IN），但仍然没有联接。 - Rowan
1个回答
1
在beta版中，.populate()实现被故意简化以支持最广泛的适配器。在最终的v0.10版本中，支持的“核心”SQL适配器（sails-mysql和sails-postgresql）将执行实际的联接操作。
@sgress454 有更新吗？我刚刚安装了 sails 0.10.2 和 sails-mysql 0.10.4，mysql 日志显示以下输出：(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
这是因为你正在访问蓝图路由，其默认限制为30条记录。在这种情况下，你不能进行简单的连接操作，因为查询结果只会返回30行，而你实际上想要的是多达900行（30个国家，每个国家有30个城市）。因此需要使用UNION，这仍然比为每个国家执行单独的查询要好得多。如果你能将其简化为一个查询，我们非常乐意看到它！请参阅此文章以获取更多有关该问题的信息。 - sgress454
网页内容由stack overflow 提供, 点击上面的
可以查看英文原文，
原文链接