helpers functions for ordering in drizzle orm

order.ts

import type {
	AnySQLiteColumn,
	BaseSQLiteDatabase,
	SQLiteColumn,
	SQLiteTableWithColumns,
	SQLiteUpdateSetSource,
} from "drizzle-orm/sqlite-core";
import { db } from "..";
import { and, eq, gte, lte, max, not, sql, type AnyColumn, type SQL } from "drizzle-orm";

const decrement = (column: AnyColumn, value = 1) => {
	return sql`${column} - ${value}`;
};

const increment = (column: AnyColumn, value = 1) => {
	return sql`${column} + ${value}`;
};

type AnySQLiteDatabase = BaseSQLiteDatabase<"sync" | "async", any, any>;

type AnySQLiteInteger<Name extends string = string> = SQLiteColumn<
	{
		name: Name;
		tableName: string;
		dataType: "number";
		columnType: "SQLiteInteger";
		data: number;
		driverParam: number;
		notNull: boolean;
		hasDefault: boolean;
		isPrimaryKey: boolean;
		isAutoincrement: boolean;
		hasRuntimeDefault: boolean;
		enumValues: undefined;
		generated: undefined;
	},
	object
>;

type SQLiteColumnsWith<
	Name extends string,
	Value extends SQLiteColumn,
> = Record<string, SQLiteColumn> & {
	[K in Name]: Value;
};

type AnySQLiteTableWithColumns<
	Columns extends Record<string, AnySQLiteColumn> = Record<
		string,
		AnySQLiteColumn
	>,
> = SQLiteTableWithColumns<{
	name: string;
	schema: undefined;
	columns: Columns;
	dialect: "sqlite";
}>;

export async function getNextValue<
	Columns extends SQLiteColumnsWith<ColumnName, AnySQLiteInteger<ColumnName>>,
	ColumnName extends keyof Columns & string,
>({
	table,
	on,
	where,
	db: instance = db,
}: {
	table: AnySQLiteTableWithColumns<Columns>;
	on: ColumnName;
	where?: SQL;
	db?: AnySQLiteDatabase;
}) {
	const result = await instance
		.select({ maxValue: max(table[on]) })
		.from(table)
		.where(where)
		.get();

	return (result?.maxValue ?? -1) + 1;
}

export async function shiftOrderedValue<
	Columns extends SQLiteColumnsWith<ColumnName, AnySQLiteInteger<ColumnName>>,
	ColumnName extends keyof Columns & string,
>({
	operation: operationString,
	to,
	from,
	where,
	table,
	on,
	db: instance = db,
}: {
	operation: "decrement" | "increment";
	table: AnySQLiteTableWithColumns<Columns>;
	on: ColumnName;
	where?: SQL;
	db?: AnySQLiteDatabase;
	to?: number;
	from?: number;
}) {
	const operation = operationString === "increment" ? increment : decrement;

	await instance
		.update(table)
		.set({ [on]: operation(table[on]) } as SQLiteUpdateSetSource<typeof table>)
		.where(
			and(
				where,
				to ? lte(table[on], to) : undefined,
				from ? gte(table[on], from) : undefined,
			),
		);
}

export async function moveOrderedValue<
	Columns extends SQLiteColumnsWith<
		ValueColumnName & GroupColumnName,
		AnySQLiteInteger<ValueColumnName> & AnySQLiteColumn
	>,
	ValueColumnName extends keyof Columns & string,
	GroupColumnName extends keyof Columns & string,
>({
	newValue,
	newGroup: newGroupId,
	where: whereItem,
	table,
	on: valueColumn,
	groupBy: groupColumn,
	db: instance = db,
}: {
	newValue: number;
	newGroup?: Columns[GroupColumnName]["_"]["data"];
	on: ValueColumnName;
	where: SQL;
	groupBy: GroupColumnName;
	table: AnySQLiteTableWithColumns<Columns>;
	db?: AnySQLiteDatabase;
}) {
	return await instance.transaction(async (tx) => {
		const original = await tx
			.select({
				value: table[valueColumn],
				groupId: table[groupColumn],
			})
			.from(table)
			.where(whereItem)
			.get();

		if (!original) return;

		const originalValue = original.value;

		const groupId = original.groupId;
		if (groupId === newGroupId) newGroupId = undefined;

		const [result] = await tx
			.select({
				maxValue: max(table[valueColumn]),
			})
			.from(table)
			.where(eq(table[groupColumn], newGroupId ?? groupId));

		const maxValue = result.maxValue ?? 0;
		newValue = Math.min(newValue, newGroupId ? maxValue + 1 : maxValue);

		await tx
			.update(table)
			.set({
				[valueColumn]: newValue,
				[groupColumn]: newGroupId,
			} as SQLiteUpdateSetSource<typeof table>)
			.where(whereItem);

		if (newGroupId) {
			await shiftOrderedValue({
				operation: "decrement",
				table,
				on: valueColumn,
				from: originalValue,
				where: eq(table[groupColumn], groupId),
				db: tx,
			});
		}

		const currentGroupId = newGroupId ?? groupId;
		const whereGroupWithoutItem = and(
			not(whereItem),
			eq(table[groupColumn], currentGroupId),
		);

		if (originalValue < newValue && !newGroupId) {
			await shiftOrderedValue({
				operation: "decrement",
				from: originalValue,
				to: newValue,
				on: valueColumn,
				table,
				db: tx,
				where: whereGroupWithoutItem,
			});
		} else {
			await shiftOrderedValue({
				operation: "increment",
				from: newValue,
				to: newGroupId ? undefined : originalValue,
				on: valueColumn,
				table,
				db: tx,
				where: whereGroupWithoutItem,
			});
		}
	});
}