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 });
}
*/
}
I'll describe the problem and the solution in SQL for simplicity and leave the knex conversion to the interested reader.
If you do:
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.