sql queries

2 files changed, 33 insertions, 1 deletions

diff --git a/apps/web/src/actions/db.ts b/apps/web/src/actions/db.ts
index 0b5bbaf6..46e3ddf6 100644
--- a/apps/web/src/actions/db.ts
+++ b/apps/web/src/actions/db.ts
@@ -7,10 +7,36 @@ import {
   StoredContent,
   storedContent,
   users,
+	space
 } from "@/server/db/schema";
-import { like, eq, and } from "drizzle-orm";
+import { like, eq, and, sql } from "drizzle-orm";
+import { union } from "drizzle-orm/sqlite-core"
 import { auth as authOptions } from "@/server/auth";
 
+// @todo: (future) pagination not yet needed
+export async function searchMemoriesAndSpaces(userId: string, query: string) {
+	const searchMemoriesQuery = db.select({
+		type: sql<string>`'memory'`,
+		space: sql`NULL`,
+		memory: storedContent as any
+	}).from(storedContent).where(and(
+		eq(storedContent.user, userId),
+		like(storedContent.title, `%${query}%`)
+	))
+
+	const searchSpacesQuery = db.select({
+		type: sql<string>`'space'`,
+		space: space as any,
+		memory: sql`NULL`,
+	}).from(space).where(
+		and(
+			eq(space.user, userId),
+			like(space.name, `%${query}%`)
+		)
+	)
+
+	return await union(searchMemoriesQuery, searchSpacesQuery)
+}
 
 async function getUser() {
   const token =
diff --git a/apps/web/src/app/page.tsx b/apps/web/src/app/page.tsx
index f3dc1bd4..97997d12 100644
--- a/apps/web/src/app/page.tsx
+++ b/apps/web/src/app/page.tsx
@@ -13,6 +13,7 @@ import { redirect } from "next/navigation";
 import { fetchContentForSpace, fetchFreeMemories, transformContent } from "../../types/memory";
 import { MemoryProvider } from "@/contexts/MemoryContext";
 import Content from "./content";
+import { searchMemoriesAndSpaces } from "@/actions/db";
 
 export const runtime = "edge";
 
@@ -68,6 +69,11 @@ export default async function Home() {
 	// freeMemories
 	const freeMemories = await fetchFreeMemories(userData.id)
 
+	// @dhravya test these 3 functions
+	fetchFreeMemories
+	fetchContentForSpace
+	searchMemoriesAndSpaces
+
   collectedSpaces.push({
     id: 1,
     name: "Cool tech",