Optimize the queries fetching albums/files

3 files changed, 37 insertions, 48 deletions

diff --git a/src/api/routes/albums/albumFullGET.js b/src/api/routes/albums/albumFullGET.js
index f92f9ae..93b56ce 100644
--- a/src/api/routes/albums/albumFullGET.js
+++ b/src/api/routes/albums/albumFullGET.js
@@ -13,33 +13,12 @@ class albumGET extends Route {
 		const album = await db.table('albums').where({ id, userId: user.id }).first();
 		if (!album) return res.status(404).json({ message: 'Album not found' });
 
-		/*
-			Grab the files in a very unoptimized way. (This should be a join between both tables)
-		*/
-		const fileList = await db.table('albumsFiles').where('albumId', id).select('fileId');
-		const fileIds = fileList.map(el => el.fileId);
-		const files = await db.table('files')
-			.whereIn('id', fileIds)
-			.orderBy('id', 'desc');
+		const files = await db.table('albumsFiles')
+			.where({ albumId: id })
+			.join('files', 'albumsFiles.fileId', 'files.id')
+			.select('files.id', 'files.name')
+			.orderBy('files.id', 'desc');
 
-		for (const file of files) {
-			file.albums = [];
-			const albumFiles = await db.table('albumsFiles')
-				.where('fileId', file.id);
-			if (!albumFiles.length) continue;
-
-			for (const albumFile of albumFiles) {
-				const album = await db.table('albums')
-					.where('id', albumFile.albumId)
-					.select('id', 'name')
-					.first();
-				if (!album) continue;
-				file.albums.push(album);
-			}
-		}
-		/*
-			For each file, create the public link to be able to display the file
-		*/
 		for (let file of files) {
 			file = Util.constructFilePublicLink(file);
 		}
diff --git a/src/api/routes/albums/albumsGET.js b/src/api/routes/albums/albumsGET.js
index bbd3cae..bbaa518 100644
--- a/src/api/routes/albums/albumsGET.js
+++ b/src/api/routes/albums/albumsGET.js
@@ -13,49 +13,37 @@ class albumsGET extends Route {
 			for anyone consuming the API outside of the lolisafe frontend.
 		*/
 		const albums = await db.table('albums')
-			.where('userId', user.id)
-			// .where('enabled', true)
-			.select('id', 'name', 'createdAt', 'editedAt');
+			.where('albums.userId', user.id)
+			.select('id', 'name', 'editedAt');
 
 		for (const album of albums) {
-			// TODO: Optimize the shit out of this.
+			// TODO: Optimize the shit out of this. Ideally a JOIN that grabs all the needed stuff in 1 query instead of 3
 
-			/*
-				Fetch every public link the album has
-			*/
-			const links = await db.table('links').where('albumId', album.id); // eslint-disable-line no-await-in-loop
+			// Fetch every public link the album has
+			// const links = await db.table('links').where('albumId', album.id); // eslint-disable-line no-await-in-loop
 
-			/*
-				Fetch the total amount of files each album has.
-			*/
+			// Fetch the total amount of files each album has.
 			const fileCount = await db.table('albumsFiles') // eslint-disable-line no-await-in-loop
 				.where('albumId', album.id)
 				.count({ count: 'id' });
 
-			/*
-				Fetch the file list from each album but limit it to 5 per album
-			*/
+			// Fetch the file list from each album but limit it to 5 per album
 			const filesToFetch = await db.table('albumsFiles') // eslint-disable-line no-await-in-loop
 				.where('albumId', album.id)
 				.select('fileId')
 				.orderBy('id', 'desc')
 				.limit(5);
 
-			/*
-				Fetch the actual files
-			*/
+			// Fetch the actual files
 			const files = await db.table('files') // eslint-disable-line no-await-in-loop
 				.whereIn('id', filesToFetch.map(el => el.fileId))
-				.select('id', 'name', 'hash', 'original', 'size', 'type', 'createdAt', 'editedAt');
+				.select('id', 'name');
 
-			/*
-				Fetch thumbnails and stuff
-			*/
+			// Fetch thumbnails and stuff
 			for (let file of files) {
 				file = Util.constructFilePublicLink(file);
 			}
 
-			album.links = links;
 			album.fileCount = fileCount[0].count;
 			album.files = files;
 		}
diff --git a/src/api/routes/albums/link/linksGET.js b/src/api/routes/albums/link/linksGET.js
new file mode 100644
index 0000000..edab49a
--- /dev/null
+++ b/src/api/routes/albums/link/linksGET.js
@@ -0,0 +1,22 @@
+const Route = require('../../../structures/Route');
+
+class linkPOST extends Route {
+	constructor() {
+		super('/album/:id/links', 'get');
+	}
+
+	async run(req, res, db, user) {
+		const { id } = req.params;
+		if (!id) return res.status(400).json({ message: 'Invalid id supplied' });
+
+		const links = await db.table('links')
+			.where({ albumId: id, userId: user.id });
+
+		return res.json({
+			message: 'Successfully retrieved links',
+			links
+		});
+	}
+}
+
+module.exports = linkPOST;