Sails JS Waterline 连接多个模型 [英] Sails JS Waterline join of multiple models

问题描述

我正在尝试使用填充方法连接多个表，我用谷歌搜索并找不到这样做的有效方法，我不想多次查询 db 来构建结果，是否可以使用风帆版本~0.10.0-rc7"来解决它我正在构建具有数百个表的退出大项目.

Hi i'm trying to join multiple tables with populate method, i googled and couldn't find efficient way of doing it, i do not want to query db several times to build the result, is it possible to solve it with sails version "~0.10.0-rc7" i'm building quit big project with more then hundred of tables.

var co = {
    adapter: 'someMysqlServer',
    migrate:'safe',
    autoCreatedAt: false,
    autoUpdatedAt: false,
    autoPK:false,
    attributes:{
        id:{
            type:"int",
            primaryKey: true,
            autoIncrement: true
        },
        code:"string",
        priority :"int",
        co_group_c_id :"int",
        timezone_c_id :"int",
        lang_c_id :"int",
        currency_c_id :"int",
        name_used :"string",
        name_official :"string",
        co_tax_no :"int",
        co_vat_no :"int",
        co_vat_reg :"int",
        co_reg_record :"string",
        co_representative :"string",
        co_addresses:{
            collection: "co_address",
            via: "co_user_id"
        },
    }
};
module.exports = co;

var co_address = {
    adapter: 'someMysqlServer',
    migrate:'safe',
    autoCreatedAt: false,
    autoUpdatedAt: false,
    autoPK:false,
    attributes: {
        id:{
            type:"int",
            primaryKey: true,
            autoIncrement: true,
        },
        address_c_id:"int" ,
        address_street_first: "string",
        address_street_second: "int",
        address_street_third: "int",
        address_postalcode: "string",
        address_city: "string",
        co_user_id: {
            model: 'co_user'
        },
        co_id: {
            model: 'co'
        },
        co_address_opening_hours:{
            collection: "co_address_opening_hours",
            via: "co_address_id"
        },
    }
};
module.exports = co_address;

var co_address_opening_hours = {
    adapter: 'someMysqlServer',
    migrate:'safe',
    autoCreatedAt: false,
    autoUpdatedAt: false,
    autoPK:false,
    attributes:{
        id:{
            type:"int",
            primaryKey: true,
            autoIncrement: true
        },
        day_c_id: "int",
        time_from: "datetime",
        time_to :"datetime",
        co_address_id: {
            model: 'co_address'
        }
    }
};
module.exports = co_address_opening_hours;

//controller
    get_co:function(req,res){
        co.find()
            .populate("co_addresses")
            .populate("co_address_opening_hours")
            .exec(function(e, company) {
                if(e) console.log(e);
                console.log(company);
                res.json(company);
    })

推荐答案

在 SailsJS 0.10+ 中，您可以使用模型关联来进行数据库连接.您可以在此处阅读有关它们的更多信息:http://sailsjs.com/documentation/概念/模型和形式/关联

In SailsJS 0.10+ you can use model associations to do database joins. You can read more about them here: http://sailsjs.com/documentation/concepts/models-and-orm/associations

基本上你首先在你的模型中定义一个关联；

Basically you first define an association in your model;

var someModel = {
  attributes: {
    name: {
      type: 'text'
    }
  }
};

var someOtherModel = {
  attributes: {
    name: {
      type: 'text'
    },
    associationProp: {
      model: 'someModel'
    }
  }
};

在上面的代码中，someOtherModel 包含与 someModel 的关联(关系).要进行连接查询，您可以使用 .populate() 方法.例如检索所有 someOtherModel 实体并填充关联属性；

In the code above someOtherModel contains association (relation) to someModel. To do a join query you can use .populate() method. For example retrieve all someOtherModel entities and populate associative properties;

someOtherModel.find().populate('associationProp').exec(...);

对于 MySQL 和 PSQL 适配器，还有 .query() 方法可用，您可以在其中编写一些要执行的手写 SQL 查询(这也适用于 Sais <0.10)；

For MySQL and PSQL adapters there's also .query() method available where you can write some hand written SQL queries to be executed (this also works in sails <0.10);

Model.query(<sql query>, <optional data>, callback);

这篇关于Sails JS Waterline 连接多个模型的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！