Michael Posts - Answer Overflow

Michael

Explore posts from servers

•Created by Michael on 5/31/2024 in #help

Passing column values through custom TypeScript functions ...

I am attempting to do this:

const hz = db.$with('sq').as(
    db
      .select({
        alt: sql<number>`cast(${
          convertEquatorialToHorizontal(datetime, observer, {
            ra: observations.ra,
            dec: observations.dec
          }).alt
        } as float)`.as('alt')
      })
      .from(observations)
  )

const hz = db.$with('sq').as(
    db
      .select({
        alt: sql<number>`cast(${
          convertEquatorialToHorizontal(datetime, observer, {
            ra: observations.ra,
            dec: observations.dec
          }).alt
        } as float)`.as('alt')
      })
      .from(observations)
  )

Essentially, I want to pass through the Column values evaluated as numbers to a function which can calculate a value for every record in the database (altitude is essentially something that changes for any given Datetime, so it can not be stored on the table itself). I was wondering if anyone could advise on how this could be possibly done ... N.B. If it is of any use, I am using sqlite ...

2 replies

DTDrizzle Team

•Created by Michael on 10/12/2023 in #help

SQLite performing a like is not working as expected ...

So I have a record in my DB where, e.g., the schema field "name" is equal to "Sirius" for one record. I then query as follows:

  const results = await db
    .select()
    .from(bodies)
    .where(
      or(
        like(bodies.iau, `%${sql.placeholder<string>('name')}%`),
        like(bodies.name, `%${sql.placeholder<string>('name')}%`),
        like(bodies.constellation, `%${sql.placeholder<string>('name')}%`)
      )
    )
    .all({
      name: `${search?.toLowerCase() || ''}`
    })

  const results = await db
    .select()
    .from(bodies)
    .where(
      or(
        like(bodies.iau, `%${sql.placeholder<string>('name')}%`),
        like(bodies.name, `%${sql.placeholder<string>('name')}%`),
        like(bodies.constellation, `%${sql.placeholder<string>('name')}%`)
      )
    )
    .all({
      name: `${search?.toLowerCase() || ''}`
    })

As far as I am aware, the like operation in SQLite behaves as an ilike in other db implementations, but for some reason ... this query is returning zero results. I'm trying to reference the documentation to understand what the underlying query is, but can't seem to reference it. But I'm also scratching my head as to what I have done wrong here? Help very much appreciated! 🙂

2 replies

DTDrizzle Team

•Created by Michael on 7/12/2023 in #help

XCannot be named without a reference to '../../../../../db/node_modules/drizzle-orm/driver.d-f4e534

I have a monorepo with a dedicated database package that handles everything around the database, and then exports a db "connection" object , and also some ORM methods. I associated packages ... I get this error when importing from that package ... any ideas welcome.

4 replies

DTDrizzle Team

•Created by Michael on 6/19/2023 in #help

SQLITE_ERROR: near "ilike": syntax error

I've currently setup an my SQL query as follows:

export const getObservationsByUserId = async (
  db: LibSQLDatabase,
  uid: string,
  search?: string,
  page?: Pagination
): Promise<Observation[]> => {
  const { limit, offset } = page || { limit: 10, offset: 0 }

  const observationsForUserId = db.select().from(observations).where(eq(observations.owner, uid))

  if (search) {
    observationsForUserId.where(ilike(observations.name, `%${search}%`))
  }

  return await observationsForUserId.limit(limit).offset(offset).all()
}

export const getObservationsByUserId = async (
  db: LibSQLDatabase,
  uid: string,
  search?: string,
  page?: Pagination
): Promise<Observation[]> => {
  const { limit, offset } = page || { limit: 10, offset: 0 }

  const observationsForUserId = db.select().from(observations).where(eq(observations.owner, uid))

  if (search) {
    observationsForUserId.where(ilike(observations.name, `%${search}%`))
  }

  return await observationsForUserId.limit(limit).offset(offset).all()
}

However, I am seeing this error: SQLITE_ERROR: near "ilike": syntax error ... the search term is validated as a string, and I am using the %{search}% syntax, I'm wondering if this is a bug or is there something quirky I need to do to get ilike to work? (I've imported) ...

6 replies

Gaming

Programming