Pagination problem using knex and objection.js

Question

My pagination works just fine without relations. But if I add images relation, I loose one post row. If I use only 1 image per post, no problem. I don't think the problem is with my join table, because without pagination I get all posts with all images..

EDIT : I just made one more post with 7 images, and now, I get only one post (9 recorded in database...). After that, if I add 2 other posts with one image per post, I get 3 posts. The post with 7 images and the 2 new ones...

page = 1 and itemsPerPage = 8.

EDIT 2 (new get all function) :

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

  try {
    const paginateCreations = await Creation.query()
      .limit(itemsPerPage)
      .offset((page - 1) * itemsPerPage)
      .orderBy("pos", "desc");

    const promises = paginateCreations.map((paginateCreation) =>
      paginateCreation.$query().withGraphJoined("images")
    );

    const creationsWithImages = await Promise.all(promises);

    creationsWithImages.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(creationsWithImages);
  } catch (error) {
    return res.status(400).json({ error: error.message });
  }
}

My get all fonction :

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) => {
        // Add image path to creations object
        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 migration :

  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 migration :

  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();
      });
    }
  });

Post Images pivot migration :

  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 model :

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",

            // If you have a model class for the join table
            // you can specify it like this:
            //
            // modelClass: PersonMovie,

            // Columns listed here are automatically joined
            // to the related models on read and written to
            // the join table instead of the related table
            // on insert/update.
            //
            // extra: ['someExtra']
          },
          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",

            // If you have a model class for the join table
            // you can specify it like this:
            //
            // modelClass: PersonMovie,

            // Columns listed here are automatically joined
            // to the related models on read and written to
            // the join table instead of the related table
            // on insert/update.
            //
            // extra: ['someExtra']
          },
          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(),
        },

        // Properties defined as objects or arrays are
        // automatically converted to JSON strings when
        // writing to database and back to objects and arrays
        // when reading from database. To override this
        // behaviour, you can override the
        // Model.jsonAttributes property.
      },
    };
  }
}

Post Images model :

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",

            // If you have a model class for the join table
            // you can specify it like this:
            //
            // modelClass: PersonMovie,

            // Columns listed here are automatically joined
            // to the related models on read and written to
            // the join table instead of the related table
            // on insert/update.
            //
            // extra: ['someExtra']
          },
          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(),
        },

        // Properties defined as objects or arrays are
        // automatically converted to JSON strings when
        // writing to database and back to objects and arrays
        // when reading from database. To override this
        // behaviour, you can override the
        // Model.jsonAttributes property.
      },
    };
  }

  /*
  $parseDatabaseJson(json) {
    json = super.$parseDatabaseJson(json);
    let location = json.location;
    if (location) {
      location = JSON.parse(location);
    }
    return Object.assign({}, json, { location });
  }
  */
}

The key issue is what query limit is being applied to. If you're do a query where you join two (or more) tables then limit is applied to the joined result, not the primary table. — Michel Floyd
– Michel Floyd, Commented Dec 13, 2023 at 18:05

Michel Floyd · Accepted Answer · 2023-12-13 18:50:35Z

1

I'll describe the problem and the solution in SQL for simplicity and leave the knex conversion to the interested reader.

If you do:

select * from A join B on b.a_id = a.id limit 10

You will get 10 total combined records. You could have 1 unique A and 10 related Bs or 3 unique A with 2,3,5 B records respectively or many other combinations.

You're trying to set the limit on the number of A records and then pick all the matching B records.

select * from (select * from A limit 10) as AA join B on b.a_id = AA.id

answered Dec 13, 2023 at 18:50

Michel Floyd

20.3k4 gold badges27 silver badges45 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Jeje Pro Over a year ago

Oh okay, I'll try

Tyler2P · Accepted Answer · 2023-12-16 21:43:25Z

I needed to make 2 distinct query because .limit() was applied to joined result.

The answer:

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

  try {
    const paginateCreations = await Creation.query()
      .limit(itemsPerPage)
      .offset(0)
      .orderBy("pos", "asc");

    const promises = paginateCreations.map((paginateCreation) =>
      paginateCreation.$query().withGraphJoined("images")
    );

    const creationsWithImages = await Promise.all(promises);

    creationsWithImages.forEach((creation) => {
      creation.images.forEach((image) => {
        let path = `assets/creations/${image.name}`;
        image.cover === 1
          ? (creation.coverUrl = `${req.protocol}://${req.get("host")}/${path}`)
          : "";
      });
    });

    // Sort creations with images by pos desc
    const sortedCreations = creationsWithImages.sort((a, b) => b.pos - a.pos);

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

Collectives™ on Stack Overflow

Pagination problem using knex and objection.js

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related