Watermelondb: Search Query with combination of two different fields

[raza2022]

Hello Dev, first of all thanks for the great work, and please consider I just started with watermelon DB and comes from Documents DB sides (Mongo etc.)
I have a very simple use case, but can't find the exact answer for this
I have two fields in users modal named name and lastname and I want to match that fields with single search field, which usually used for user input in forms, so here is exact issue, I can search easily if there is a single word search by user, either first name or last name, i.e let assume the below use case
name : jhon
lastname: cena`
so that code works fine

data = await db.collections
    .get('users').query(
        Q.where('id', Q.notEq(user.id)),
        Q.or(
            Q.where('name', Q.like(`%${ customSanitizeLikeString(name) }%`)),
            Q.where('lastname', Q.like(`%${ customSanitizeLikeString(lastname) }%`))
        )
    ).fetch();

but here I assumed that name and last name should be only one word and split the input wich space but what is first name has multiple words like below
name : jhon cena
lastname: jhon papa
and I have to match the exact fullname or at least the all words entered, means exact first and last name or the maximum words as full name I know I can do it with with and instead of or but the issue is search box is single input fields and I don't know where first name ends, and last name starts, so I have to do full name like search so what's the best way to do so? as I don't know which after which space last name start in input field?
what is used to do these like cases in mongo is to create a new fields one the fly (shadow fields )like full_name which combine both fields and perform search like this

.get('users').query(
        Q.where('id', Q.notEq(user.id)),
        Q.where('name', Q.like(`%${ customSanitizeLikeString(fullName) }%`)),
    ).fetch();

I didn't found anything is watermelon db to combine fields like full_name on the fly like I do in mongoose, or i might missed some parts, and also suggest if there is any better way to achive the full name search without adding an extra fields as I don't really wanna a duplicate field on creating/updating any document just for simple search purpose, thanks in advance

[raza2022]

for extra reference I can achive the same thing in mongo db with code similer like this without create any extra fields for full name

db.collection.aggregate([
{
    $match: {
        // Optional criteria to select only some documents to process, such as...
        deleted: null
    }
},
{
    $addFields: {
        // Need to prefix fields with '$'
        fullName: { $concat: [ "$firstName", "$lastName" ] },
    }
},
{
    $search: { fullName: /.*vakid kh.*/ },
}
]);

@radex please give me the only hint when you have time, if I can achive the same in water melon db Thanks

[radex]

please give me the only hint when you have time, if I can achive the same in water melon db Thanks

Do you only need to support iOS/Android, or are you targeting web too? If only native, then you can use Q.unsafeSqlExpr() in your query to do this with SQL, I think.

[raza2022]

@radex yes I need to support only mobile application, I saw the unsafe sql query in docs too, so it's means there is no native implementation that I can perform combined search for two different fields?

[radex]

nope; I'd use a small snippet of SQL for this. Note that you don't need to make the whole query a SQL, just this expression that runs LIKE on two concatenated fields.

BTW: I would say that it's a bad practice to keep "first name" and "last name" as separate fields in the database, because, quite simply, not all names work like this… If you have any influence over this, I'd recommend that it is STORED concatenated. https://www.kalzumeus.com/2010/06/17/falsehoods-programmers-believe-about-names/

[raza2022]

@radex thanks for your response, I will consider the naming with my team lead, one last thing I assumed that Concat available by defaut, correct me if I am wrong
let query =CONCAT(name, ' ', lastname) like '${ text }%';
but I am getting error about concat function below is my full query

data = await db.collections
    .get('users').query(
        Q.where('id', Q.notEq(user.id)),
        Q.unsafeSqlExpr(query)
    ).fetch();

[radex]

@raza2022 it took me 30 seconds to find the answer to your error, please do put a little more effort when asking others to help you out for free -- https://www.sqlitetutorial.net/sqlite-string-functions/sqlite-concat/

[vishgoel007]

@raza2022 Hi, can you please tell me how did you connect WaterlemoDb with Reactotron ?