Drizzle Team•15mo ago

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! 🙂

1 Reply

MichaelOP•15mo ago

As a direct comparison, swopping out the where for this:

.where(
      or(
        sql`lower(${bodies.iau}) like ${sql.placeholder('name')}`,
        sql`lower(${bodies.name}) like ${sql.placeholder('name')}`,
        sql`lower(${bodies.constellation}) like ${sql.placeholder('name')}`
      )
    )

.where(
      or(
        sql`lower(${bodies.iau}) like ${sql.placeholder('name')}`,
        sql`lower(${bodies.name}) like ${sql.placeholder('name')}`,
        sql`lower(${bodies.constellation}) like ${sql.placeholder('name')}`
      )
    )

... works absolutely fine

Gaming

Programming

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