Gorka Cesium•10mo ago

is it possible to search by prefix only?

I need to search exact matches that start with a code. example search text : 13910 and expected results: - 13910-1 - 13910-2 Currently it gives the first results plus a bunch of other results with fuzzy search. In this case i would like to do a prefix search only. I was thinking about using filter but not sure how to achieve startsWith with filter

14 Replies

Gorka CesiumOP•10mo ago

the convex bot suggested to use the filter with q.gte("fieldname", "13910").lt("fieldname", "13910-10")

erquhart•10mo ago

Exactly what I was going to suggest, you’ll just want to tweak the specific strings a bit (assuming “-10” may not be the actual maximum value you want to use) You’ll probably want to replace that second value with “13911”

Gorka CesiumOP•10mo ago

ok so i ended up with using search + filter

let existing = await db
        .query('quotes')
        .withSearchIndex('search_referenceCode', (q) =>
          q.search('referenceCode', referenceCode),
        )
        .filter((q) =>
          q.and(
            q.gte('referenceCode', referenceCode || ''),
            q.lt('referenceCode', referenceCode + '-10'),
          ),
        )
        .collect()

let existing = await db
        .query('quotes')
        .withSearchIndex('search_referenceCode', (q) =>
          q.search('referenceCode', referenceCode),
        )
        .filter((q) =>
          q.and(
            q.gte('referenceCode', referenceCode || ''),
            q.lt('referenceCode', referenceCode + '-10'),
          ),
        )
        .collect()

erquhart•10mo ago

Note that, given code 13910, 13910-10 and above would be excluded with that filter

Gorka CesiumOP•10mo ago

oh good to know! i'll update that, thanks

lee•10mo ago

Cool solution! I think a standard index would probably be a little faster. A regular index would allow more than 1024 results, and would put the codes in lexicographic order instead of relevance order. Maybe we should write up a pattern for using a regular index to search for a string prefix

Gorka CesiumOP•10mo ago

i would like to take a look at that approach

lee•10mo ago

It's basically what the convex bot said index("referenceCode", ["referenceCode"]) in the schema await db.query("quotes").withIndex("referenceCode", q=>q.gte("referenceCode", prefix).lte("referenceCode", prefix + "~") Note "~" is pretty close to the end of the ascii table

Gorka CesiumOP•10mo ago

thanks, I'll try that too, good idea I didnt know it's possible to use gte in index btw I noticed I could not chain the .gte and .lte. I had to wrap them in an .and

lee•10mo ago

In a .filter you can't chain .gte with .lte, but in a .withIndex you should be able to. In a .withIndex there is no .and

erquhart•10mo ago

Totally missed that a filter was being used, should definitely be an index

Gorka CesiumOP•10mo ago

good to know so with this approach I don't need to additionally do withSearch ?

erquhart•10mo ago

Right, just a regular index.

Gorka CesiumOP•10mo ago

ok perfect

is it possible to search by prefix only?

Did you find this page helpful?