javascript - 如何使用不同表中的列数据从其他表中获取数据？

Question

我有两个表:

表1

id | email
1 | email1
2 | email2

表2

userid | username
2      | user1
3      | user2

现在，使用 sails.js 关联，我想这样做:

我有一个用户名 = user1。使用此功能，首先我需要此 user1 的 userid，然后使用此 userid，我想访问 table1 中的电子邮件。

我只想使用模型、 Controller 和关联来做到这一点。可能吗？

编辑:

我上面提供的表格是我原始表格的示例表格。在这里，我发布了实际表格的实际模型:

Corporate_info.js(对于table1。实际表名称:corporate_info)

module.exports = {
  tableName: 'corporate_info',
  autoCreatedAt: false,
  autoUpdatedAt: false,
  connection: 'mysqlAdapter',
  attributes: {
    id: {
      type: 'integer',
      required: true,
      autoIncrement: true,
      primaryKey: true,
      size: 11
    },
    fname: {
      type: 'string',
      required: true,
      size: 100
    },
    lname: {
      type: 'string',
      required: true,
      size: 100
    },
    country_code: {
      type: 'string',
      required: true,
      size: 45
    },
    mobile: {
      type: 'string',
      required: true,
      unique: true,
      size: 100
    },
    email: {
      type: 'string',
      required: true,
      size: 100
    },
    address: {
      type: 'string',
      required: true,
      size: 100
    },
    userid: {
      type: 'integer',
      required: false,
      size: 11
    },
    imei_number: {
      type: 'string',
      required: false
    },
    owner: {
      model: 'Rc_users',
      unique: true
    }

  }
};

Rc_users.js(对于table2。实际表名称:rc_users)

module.exports = {
  tableName: 'rc_users',
  autoCreatedAt: false,
  autoUpdatedAt: false,
  connection: 'mysqlAdapter',
  attributes: {
    id: {
      type: 'integer',
      required: true,
      autoIncrement: true,
      primaryKey: true,
      size: 11
    },
    country_code: {
      type: 'string',
      required: true,
      size: 45
    },
    username: {
      type: 'string',
      required: true,
      unique: true,
      index: true,
      size: 255
    },
    password: {
      type: 'string',
      required: true,
      size: 40
    },
    code: {
      type: 'string',
      required: true,
      index: true,
      size: 40
    },
    active: {
      type: 'string',
      required: true,
      size: 3
    },
    last_login: {
      type: 'integer',
      required: true,
      index: true,
      size: 11
    },
    last_session: {
      type: 'string',
      required: true,
      index: true,
      size: 40
    },
    blocked: {
      type: 'string',
      required: true,
      size: 3
    },
    tries: {
      type: 'integer',
      required: true,
      size: 2
    },
    last_try: {
      type: 'integer',
      required: true,
      index: true,
      size: 11
    },
    email: {
      type: 'string',
      required: true,
      size: 255
    },
    mask_id: {
      type: 'integer',
      required: true,
      size: 6
    },
    group_id: {
      type: 'integer',
      required: true,
      size: 6
    },
    activation_time: {
      type: 'integer',
      required: true,
      index: true,
      size: 11
    },
    last_action: {
      type: 'integer',
      required: true,
      index: true,
      size: 11
    },
    firstname: {
      type: 'string',
      required: false,
      size: 40
    },
    lastname: {
      type: 'string',
      required: false,
      size: 40
    },
    companyname: {
      type: 'string',
      required: false,
      size: 100
    },
    reg_type: {
      type: 'integer',
      required: true,
      size: 11
    },
    rc_web_userid: {
      type: 'string',
      required: false,
      size: 100
    },
    admin_id: {
      type: 'integer',
      required: false,
      size: 11
    },
    device_token: {
      type: 'string',
      required: false,
      size: 500
    },
    device_type: {
      type: 'string',
      required: false,
      size: 45
    },
    userMobile: {
      collection: 'corporate_info',
      via: 'owner'
    }
  }
};

Answer 1

你应该像这样创建模型

I assumed that you want to do one-by-one association. So this is it.

api/models/Email.js

attributes: {
    email : {
        type: 'email',
        unique: true
    },
    owner : {
        model:'user',  //here put your model name 
        unique: true   //put unique here because it's one by one association
   }
}

api/models/User.js

attributes: {
   username : {
     type: 'string',
     unique: true
   },
   userEmail : {
      collection:'email', //here is also model name
      via: 'owner'
   }
}

然后

从电子邮件获取用户

Email.find().populate('owner')

获取用户的电子邮件

User.find().populate('userEmail')

现在您可以从两个模型访问数据。

尝试打印上面的两个命令，您将看到您的数据包含相关表中的数据。

Email.find().populate('owner').exec(function(err, records) {
    res.json(records)
});

这是我的回复。

[
    {
        "owner": {
            "username": "test",
            "id": 1,
            "createdAt": "2016-11-23T13:45:06.000Z",
            "updatedAt": "2016-11-23T13:45:06.000Z"
        },
        "email": "test@test.com",
        "id": 1,
        "createdAt": "2016-11-23T13:45:06.000Z",
        "updatedAt": "2016-11-23T13:45:06.000Z"
    }
]

更多信息:http://sailsjs.com/documentation/concepts/models-and-orm/associations/one-to-one