如何使用来自不同表的列数据从其他表中获取数据？ [英] How to get data from other table using a column data from a different table?

问题描述

我有两张桌子：

table1

id | email
1 | email1
2 | email2

table2

userid | username
2      | user1
3      | user2

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

Now, using sails.js associations, I want to do this:

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

I have a username = user1. Using this, first I need the userid of this user1, then using this userid, I want to access the email from table1.

我想仅使用模型，控制器和关联来做到这一点。有可能吗？

I want to do this using models, controllers and associations only. Is it possible?

编辑：

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

The tables I have provided above are sample tables for my original tables. Here I'm posting my actual models for actual tables:

Corporate_info.js （对于table1。实际表名：corporate_info）

Corporate_info.js (For table1. Actual table name: 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）

Rc_users.js (for table2. Actual table name: 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'
    }
  }
};

推荐答案

你应该创建这样的模型

我假设您想要逐个关联。所以就是这样。

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

api / models / Email.js

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

api/models/User.js

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

然后

从电子邮件中获取用户

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

从用户那里获取电子邮件

Get email from user

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

现在你可以访问了来自两个模型的数据。

Now you can access to your data both from your two model.

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

Try to print two command above you will see that your data contain the data from related table.

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": "[e-mail]",
        "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

这篇关于如何使用来自不同表的列数据从其他表中获取数据？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！