使用Knex.js与PostgreSQL实现三表关联并返回嵌套结构结果

问题背景 #

我是PostgreSQL和Knex.js的新手，现有三张表：user、bank_details、social_media，需要查询出包含用户银行信息和社交媒体信息的嵌套结构结果，同时要按created_at大于指定日期筛选记录。

表结构 #

user表 #

id,
phone_number,
created_at,
updated_at

bank_details表 #

id,
user_id,
bank_name,
acc_api_key,
created_at,
updated_at

social_media表 #

id,
user_id,
media_channel_name,
channel_api_key,
created_at,
updated_at

期望的查询结果 #

[{
  id:1, 
  phone_number:'42223424234', 
  bankDetails:[
    {
      user_id:1, 
      bank_name:'abc bank', 
      acc_api_key: 'cQ54B325KB2345434'
    },
    {
      user_id:1, 
      bank_name:'cBD bank', 
      acc_api_key: 'JJJSIDD98ASD0AS'
    },
    {
      user_id:1, 
      bank_name:'EFG bank', 
      acc_api_key: '789FAF8AFFYDF9E'
    }
  ],
  socialMedia:[
    {
      user_id:1, 
      media_channel_name:'whatsapp', 
      channel_api_key: '45n2q35oi5'
    },
    {
      user_id:1, 
      media_channel_name:'facebook', 
      channel_api_key: 'q2j3i355'
    }
  ]
},
{
  id:2, 
  phone_number:'72372373828382', 
  bankDetails:[
    {
      user_id:2, 
      bank_name:'eere bank', 
      acc_api_key: 'erereac'
    },
    {
      user_id:2, 
      bank_name:'iff bank', 
      acc_api_key: '789FAF8AFFYDF9E'
    }
  ],
  socialMedia:[{
      user_id:2, 
      media_channel_name:'instagram', 
      channel_api_key: '09e8q232'
    }
  ]
}]

尝试过的查询（未得到预期效果） #

let data = await userModel.query()
        .leftJoin('bank_details', 'user.id', '=', 'bank_details.user_id')
        .leftJoin('social_media', 'user.id', '=', 'social_media.user_id')
        .select("*")

对应的Objection.js模型 #

userModel #

import { Model } from 'objection';

class userModel extends Model {

  public id!: number;
  public name!: string;
  public phone_number!: string;
  public created_at!: string;
  public updated_at!: string;

  static get tableName(): string {
    return 'user';
  }

  static get jsonSchema(): schema {
    return {
      type: 'object',
      properties: {
        id: { type: 'integer' },
        name: { type: 'string' },
        phone_number: { type: 'string' },
        member_id: { type: 'string' },
        created_at: { type: 'string' },
        updated_at: { type: 'string' },
      },
    };
  }
}

export default userModel;

bankDetailsModel #

import { Model } from 'objection';

class bankDetailsModel extends Model {

  public id!: number;
  public bank_name!: string;
  public acc_api_key!: string;
  public user_id!: number;
  public created_at!: string;
  public updated_at!: string;

  static get tableName(): string {
    return 'bank_details';
  }

  static get jsonSchema(): schema {
    return {
      type: 'object',
      properties: {
        id: { type: 'integer' },
        bank_name: { type: 'string' },
        acc_api_key: { type: 'string' },
        user_id: { type: 'integer' },
        created_at: { type: 'string' },
        updated_at: { type: 'string' },
      },
    };
  }
}

export default bankDetailsModel;

socialMediaModel #

import { Model } from 'objection';

class socialMediaModel extends Model {

  public id!: number;
  public media_channel_name!: string;
  public channel_api_key!: string;
  public user_id!: number;
  public created_at!: string;
  public updated_at!: string;

  static get tableName(): string {
    return 'social_media';
  }

  static get jsonSchema(): schema {
    return {
      type: 'object',
      properties: {
        id: { type: 'integer' },
        media_channel_name: { type: 'string' },
        channel_api_key: { type: 'string' },
        user_id: { type: 'integer' },
        created_at: { type: 'string' },
        updated_at: { type: 'string' },
      },
    };
  }
}

export default socialMediaModel;

解决方案 #

要实现嵌套结构的查询，需要利用Objection.js的withGraphFetched方法，同时在模型中定义关联关系。

步骤1：在userModel中添加关联关系 #

修改userModel，添加relationMappings来定义和bankDetails、socialMedia的关联：

import { Model } from 'objection';
import bankDetailsModel from './bankDetailsModel';
import socialMediaModel from './socialMediaModel';

class userModel extends Model {
  // 原有属性和tableName、jsonSchema不变...

  static get relationMappings() {
    return {
      bankDetails: {
        relation: Model.HasManyRelation,
        modelClass: bankDetailsModel,
        join: {
          from: 'user.id',
          to: 'bank_details.user_id'
        }
      },
      socialMedia: {
        relation: Model.HasManyRelation,
        modelClass: socialMediaModel,
        join: {
          from: 'user.id',
          to: 'social_media.user_id'
        }
      }
    };
  }
}

export default userModel;

步骤2：编写正确的查询代码 #

使用withGraphFetched来获取关联数据，同时添加created_at的筛选条件：

const targetDate = '2023-01-01'; // 替换成你的指定日期

const data = await userModel.query()
  .where('created_at', '>', targetDate)
  .withGraphFetched('[bankDetails, socialMedia]')
  .select('id', 'phone_number'); // 只选择需要的用户字段，避免冗余

说明 #

• withGraphFetched会执行单独的查询来获取关联数据，避免SQL JOIN导致的重复用户记录问题，正好符合嵌套结构需求。
• 如果需要对关联数据也做日期筛选，可以在关联后添加条件：

.withGraphFetched({
  bankDetails: (query) => query.where('created_at', '>', targetDate),
  socialMedia: (query) => query.where('created_at', '>', targetDate)
})

内容的提问来源于stack exchange，提问作者Shanthi B