使用Knex与Objection.js分页时关联图片丢失数据的问题

问题背景 #

分页功能在未关联图片时正常工作，但关联images多对多关系后，会丢失Post数据行：

• 每个Post仅关联1张图片时无异常
• 取消分页可正常获取所有带图片的Post
• 补充测试：新增关联7张图片的Post后，数据库共9条Post数据，但page=1、itemsPerPage=8的分页仅返回1条；添加两个单图Post后，返回3条（含7图Post和两个新Post）

问题原因 #

原代码中使用withGraphJoined("images")进行关联查询，这会生成笛卡尔积：一个关联了N张图片的Post会在SQL结果中返回N行数据。此时limit和offset是作用在这些行上，而非Post实体的数量，导致分页逻辑失效——比如7张图的Post占了7行，limit=8只能再容纳1行（对应另一个Post的1张图），但Objection.js合并相同Post后，实际返回的Post数量远小于预期。

解决方案 #

先分页查询Post主数据，再批量关联查询对应的图片（避免笛卡尔积影响分页），以下是两种实现方式：

优化方案1：批量查询减少N+1请求 #

export async function getAll(req, res, next) {
  const { page, itemsPerPage } = req.params;
  const pageNum = parseInt(page, 10);
  const perPage = parseInt(itemsPerPage, 10);

  try {
    // 1. 分页获取当前页的Post主数据
    const paginatedCreations = await Creation.query()
      .limit(perPage)
      .offset((pageNum - 1) * perPage)
      .orderBy("pos", "desc");

    if (paginatedCreations.length === 0) {
      return res.status(200).json([]);
    }

    // 2. 批量查询当前页所有Post对应的图片
    const creationIds = paginatedCreations.map(c => c.id);
    const allImages = await CreationImage.query()
      .join("creations_images_pivot", "creations_images.id", "creations_images_pivot.image_id")
      .whereIn("creations_images_pivot.creation_id", creationIds)
      .select("creations_images.*", "creations_images_pivot.creation_id");

    // 3. 将图片映射到对应Post并处理封面URL
    const creationsWithImages = paginatedCreations.map(creation => {
      const images = allImages.filter(img => img.creation_id === creation.id);
      images.forEach(image => {
        const path = `assets/creations/${image.name}`;
        if (image.cover) {
          creation.coverUrl = `${req.protocol}://${req.get("host")}/${path}`;
        }
      });
      return { ...creation, images };
    });

    return res.status(200).json(creationsWithImages);
  } catch (error) {
    return res.status(400).json({ error: error.message });
  }
}

优化方案2：使用Objection内置关联（代码更简洁） #

如果不介意N+1查询，可直接用withGraphFetched批量关联：

export async function getAll(req, res, next) {
  const { page, itemsPerPage } = req.params;
  const pageNum = parseInt(page, 10);
  const perPage = parseInt(itemsPerPage, 10);

  try {
    // 1. 分页获取Post主数据
    const paginatedCreations = await Creation.query()
      .limit(perPage)
      .offset((pageNum - 1) * perPage)
      .orderBy("pos", "desc");

    // 2. 批量关联图片
    const creationsWithImages = await Promise.all(
      paginatedCreations.map(creation => 
        creation.$query().withGraphFetched("images")
      )
    );

    // 3. 处理封面URL
    creationsWithImages.forEach(creation => {
      creation.images.forEach(image => {
        const path = `assets/creations/${image.name}`;
        if (image.cover) {
          creation.coverUrl = `${req.protocol}://${req.get("host")}/${path}`;
        }
      });
    });

    return res.status(200).json(creationsWithImages);
  } catch (error) {
    return res.status(400).json({ error: error.message });
  }
}

相关代码参考 #

原问题代码（getAll函数） #

export function getAll(req, res, next) {
  const [page, itemsPerPage] = [req.params.page, req.params.itemsPerPage];

  console.log(req.params);

  Creation.query()
    .withGraphJoined("images")
    .limit(itemsPerPage)
    .offset((page - 1) * itemsPerPage)
    .orderBy("pos")
    .then((creations) => {
      creations.forEach((creation) => {
        creation.images.forEach((image) => {
          let path = `assets/creations/${image.name}`;
          image.cover === 1
            ? (creation.coverUrl = `${req.protocol}://${req.get(
                "host"
              )}/${path}`)
            : "";
        });
      });
      return res.status(200).json(creations);
    })
    .catch((error) => {
      return res.status(400).json({
        error: error,
      });
    });
}

Post表迁移 #

return knex.schema.hasTable("creations").then(function (exists) {
  if (!exists) {
    return knex.schema.createTable("creations", function (table) {
      table.increments("id").primary();
      table.string("title", 50).notNullable();
      table.text("desc");
      table.decimal("pos", 8, 0);
      table
        .integer("user_id", 10)
        .unsigned()
        .notNullable()
        .references("id")
        .inTable("users");
      table.timestamps();
    });
  }
});

Post Images表迁移 #

return knex.schema.hasTable("creations_images").then(function (exists) {
  if (!exists) {
    return knex.schema.createTable("creations_images", function (table) {
      table.increments("id").primary();
      table.string("name", 100).unique().notNullable();
      table.boolean("cover").defaultTo(false);
      table.timestamps();
    });
  }
});

多对多中间表迁移 #

return knex.schema.hasTable("creations_images_pivot").then(function (exists) {
  if (!exists) {
    return knex.schema.createTable(
      "creations_images_pivot",
      function (table) {
        table
          .integer("image_id", 10)
          .unsigned()
          .notNullable()
          .references("id")
          .inTable("creations_images");
        table
          .integer("creation_id", 10)
          .unsigned()
          .notNullable()
          .references("id")
          .inTable("creations");
      }
    );
  }
});

Post模型 #

class Creation extends Model {
  static get tableName() {
    return "creations";
  }

  static get relationMappings() {
    return {
      author: {
        relation: Model.BelongsToOneRelation,
        modelClass: User,
        join: {
          from: "creations.user_id",
          to: "users.id",
        },
      },

      filters: {
        relation: Model.ManyToManyRelation,
        modelClass: CreationFilter,
        join: {
          from: "creations.id",
          through: {
            from: "creations_filters_pivot.creation_id",
            to: "creations_filters_pivot.filter_id",
          },
          to: "creations_filters.id",
        },
      },

      images: {
        relation: Model.ManyToManyRelation,
        modelClass: CreationImage,
        join: {
          from: "creations.id",
          through: {
            from: "creations_images_pivot.creation_id",
            to: "creations_images_pivot.image_id",
          },
          to: "creations_images.id",
        },
      },
    };
  }

  static get jsonSchema() {
    return {
      type: "object",
      required: ["title", "user_id"],

      properties: {
        id: { type: "integer" },
        title: { type: "string", maxLength: 50 },
        desc: { type: "string" },
        pos: { type: "number" },
        user_id: { type: "integer" },
        created_at: {
          type: "string",
          format: "date-time",
          default: new Date().toISOString(),
        },
        updated_at: {
          type: "string",
          format: "date-time",
          default: new Date().toISOString(),
        },
      },
    };
  }
}

Post Images模型 #

class CreationImage extends uniqueParams(Model) {
  static get tableName() {
    return "creations_images";
  }

  static get relationMappings() {
    return {
      creations: {
        relation: Model.ManyToManyRelation,
        modelClass: Creation,
        join: {
          from: "creations_images.id",
          through: {
            from: "creations_images_pivot.image_id",
            to: "creations_images_pivot.creation_id",
          },
          to: "creations.id",
        },
      },
    };
  }

  static get jsonSchema() {
    return {
      type: "object",
      required: ["name"],

      properties: {
        id: { type: "integer" },
        name: { type: "string", maxLength: 100, minLength: 2 },
        cover: { type: "boolean" },
        created_at: {
          type: "string",
          format: "date-time",
          default: new Date().toISOString(),
        },
        updated_at: {
          type: "string",
          format: "date-time",
          default: new Date().toISOString(),
        },
      },
    };
  }
}

内容的提问来源于stack exchange，提问作者Jeje Pro