Trying to learn how to search astrodb

[vocal olive]

I have a bunch of books that I just want to be able to search a title and see the description with pagination. The books are in an astrodb and I have it working to where I can query and show them manually. However, I am trying to get a UI search working and am a little lost.

When I edit the url directly to http://localhost:4321/?query=booktitle I am able to load a search, however, I am trying to get this to work with some sort of input on the UI side now. I tried using an <input> an onupdate or an onclick with a <button> next to the input, but am not sure best practices for loading a search. Any help for this learning newbie is appreciated! Thank you!

/src/index.astro

---
import { GET } from '../api/search.ts'

const response = await GET(Astro)

const data = await response.json()
console.log(data.books)

interface Book {
    title: string
    description: string
}

const books: Book[] = data.books

---

<h2>Website loaded</h2>

<input
    type="text"
    placeholder="Search..."
/>

{
    books.map(({ title, description }) => (
        <div>
            <p>{title}</p>
            <p>{description}</p>
        </div>
    ))
}

/src/api/search.ts

import type { APIRoute } from 'astro';
import { searchBooks } from '../search';

export const GET: APIRoute = async (context) => {
  const query  = context.url.searchParams.get('query') || '';

  const sortby = 'asc';
  const page = 0;
  const resultsPerPage = 4;

  const foundBooks = await searchBooks(query, sortby, page, resultsPerPage);

  return new Response(
    JSON.stringify({
      books: foundBooks,
    }),
    {
      headers: { 'content-type': 'application/json' }
    }
  )
}

[silver crag]

Couple of things. You don’t need an API endpoint to fetch from AstroDB. Then right now the way it’s setup. You are loading not any books into your table. So I would check out this part of the docs for populating
Your table.

https://docs.astro.build/en/guides/astro-db/#select

then this part for filtering. https://docs.astro.build/en/guides/astro-db/#filtering

another option as well since you are already loading the books into the table. Is to filter client side.

https://youtu.be/TlP5WIxVirU?si=OvQiuh3aMfboOBO0

[vocal olive]

Here is my current code for these docs. The APIroute calls the searchbooks query listed below. The big issue I am having is a way to search the books with UI and have it saved to the url such that the url can be coppied or website refreshed without loosing the query.

import { db, Books, like, and, asc, desc, isNotNull } from 'astro:db';

export async function searchBooks(query = '', sortby = 'asc', page = 0, resultsPerPage = 4) {

    const words = query.split(/\s+/);
    const numbers = query.match(/\d+/g) || [];

    const wordConditions = words.map(word => like(Books.title, `%${word}%`));
    const numberConditions = numbers.map(number => like(Books.title, `%${number}%`));

    const conditions = [...wordConditions, ...numberConditions];

    const results = query ? 
        await db.select()
            .from(Books)
            .where(and(...conditions))
            .orderBy(sortby === 'asc' ? asc(Books.title) : desc(Books.title))
            .limit(resultsPerPage).offset(page * resultsPerPage) :
        await db.select()
            .from(Books)
            .where(isNotNull(Books.title))
            .orderBy(sortby === 'asc' ? asc(Books.title) : desc(Books.title))
            .limit(resultsPerPage).offset(page * resultsPerPage);

    return results;
}