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

我有两张桌子：

table1

id | email
1 | email1
2 | email2

table2

userid | username
2      | user1
3      | user2

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

我有一个用户名 =user1。使用这个，首先我需要userid这个user1，然后使用这个userid，我想访问email from table1.

我只想使用模型、控制器和关联来做到这一点。是否可以？

EDIT:

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

公司信息.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'
    }
  }
};

你应该像这样创建模型

我假设你想要进行一对一的关联。所以就是这样。

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'
   }
}

Then

从电子邮件获取用户

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": "[email protected] /cdn-cgi/l/email-protection",
        "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 http://sailsjs.com/documentation/concepts/models-and-orm/associations/one-to-one