Drizzle Team•13mo ago

search on all `with` and `columns` fields via text (sqlite)

Hi! I'm using better-sqlite3 as the driver. I have a bunch of fields selected via columns & with (nested fields) & an offset based pagination. I'm using the querybuilder (.query). How would I go about performing a full text search on the selected columns as well as the selected nested columns? Say,

const { offset, query, limit} = input;
const result = ctx.db.query.myModel.findMany({
   limit: limit ?? 10,
   offset: offset ?? 0,
   with: ...,
   columns: ...
});
const nextOffset: number = result.length + (offset ?? 0);
return {
 data: result,
 nextOffset: nextOffset
};

const { offset, query, limit} = input;
const result = ctx.db.query.myModel.findMany({
   limit: limit ?? 10,
   offset: offset ?? 0,
   with: ...,
   columns: ...
});
const nextOffset: number = result.length + (offset ?? 0);
return {
 data: result,
 nextOffset: nextOffset
};

I'm including towards using where: or(x, or(y, or())) but i'm not sure that's correct (& would work for slected nested relations). Let me know!!

7 Replies

Angelelz•13mo ago

I can't think of any good way of achieving what you want is sql I think you are better off serching at the application level

isitayushOP•13mo ago

Thank you @Angelelz! (i apologize for ping!!) This is what I ended up doing. Do you think it's possible by building sql queries via drizzle instead of using the drizzle .query builder. I'm not too good at SQL so I had no choice other than using query builder for this but just wondering.

Angelelz•13mo ago

There are many way of searching through many columns. but I'm not sure it can be through several tables in the same query Maybe a concat with several column from a join? How big is the content in each column?

isitayushOP•13mo ago

There are many way of searching through many columns. but I'm not sure it can be through several tables in the same query Maybe a concat with several column from a join?

That's interesting! Getting the data & joining is not difficult. I'm just stuck on searching (hopefully a text search) across the result: object[] of all the joins. If I may, could you point me to a few resources for many ways of searching through many columns (for sqlite!) if you're aware of any Angelelz.

How big is the content in each column?

On most of the columns, the content is almost always 1-2 words long.

Angelelz•13mo ago

I don't know of resources honestly But try searching for a way to concat strings in sqlite I think there is the || operator

isitayushOP•13mo ago

That helps & I'll look for it! Thank you Angelelz. 💙

Angelelz•13mo ago

No problem Also, see if sqlite have support for full text search

Gaming

Programming

search on all `with` and `columns` fields via text (sqlite)