feat: add ability for escaping for Raw() find operator

[temnov98]

Currently, find operator Raw() not supported escaping.
I have updated the code so that it is now possible to use an escaping.
The updated syntax is as follows:

await connection.getRepository(Post).find({
    likes: Raw((columnAlias, parameters) => {
        return `(${columnAlias} = ${parameters[0]}) OR (${columnAlias} = ${parameters[1]})`
    }, [2, 3]),
});

await connection.getRepository(Post).find({
    likes: Raw((columnAlias, parameters) => {
        return `(${columnAlias} = ANY(${parameters[0]})) AND (${columnAlias} < ${parameters[1]})`
    }, [[1, 4, 5, 6], 6]),
});

await connection.getRepository(Post).find({
    title: Raw((columnAlias, parameters) => {
        return `${columnAlias} = ANY(${parameters[0]})`;
    }, [["About #1", "About #3", "About #5"]]),
    likes: Raw((columnAlias, parameters) => `${columnAlias} IN (${parameters[0]}, ${parameters[1]})`, [5, 1]),
});

The tests for the new functionality have been written. The old functionality works unchanged.

[temnov98]

This functionality is useful in the case when it is necessary to pass user input in a raw query. Also you can create custom operators via Raw() operator.

For example, operator ILIKE() which is in the next branch, but not in master can be implemented as follows:

function ILike(value: string) {
    return Raw((columnAlias, parameters) => `${columnAlias} ILIKE ${parameters[0]}`, [value]);
}

[imnotjames]

Aha! This is very nice! :) I've actually seen a few people asking about this BUT for the life of me I can't find the issues where that was.

I'll try to get to reviewing this over the next few days as I'm sure it would make a lotta people happy.

As I understand it - this is backwards compatible, as well? With the previous functionality? Looks to be the case there from the source.

[imnotjames]

I think this Closes #2475

[temnov98]

I added the ability to specify parameters using an object literal parameters for Raw() find operator.

Example:

await connection.getRepository(Post).find({
    likes: Raw((columnAlias) => `${columnAlias} < :maxValue`, { maxValue: 6 }),
});

[temnov98]

As I understand it - this is backwards compatible, as well? With the previous functionality? Looks to be the case there from the source.

yes, all changes are backward compatible.

[pleerock]

I think example is incorrect here

[temnov98]

I think example is incorrect here

fixed

[pleerock]

I can't understand why do we need this syntax. Isn't it redundant?

[temnov98]

I can't understand why do we need this syntax. Isn't it redundant?

Do you suggest leaving only parameters via ObjectLiteral and remove via array? I can do it.

[pleerock]

Yes, it's just seems redundant. It's better to left only one syntax if they both do same and there are no technical issues.

[temnov98]

Yes, it's just seems redundant. It's better to left only one syntax if they both do same and there are no technical issues.

Done. Waiting for the tests to complete.

[pleerock]

Thank you a lot!

[sanderevers]

Having parameters with the Raw operator is a very welcome feature for us, thanks.

I did however encounter a little catch: what happens when you use the same :name in two Raw instances? To some people it will probably be obvious that this should map to the same $parameter in the generated SQL (as it currently does). But I think you could also argue that this parameter should be local to this Raw instance.
For example, in our project, these Raw instances are automatically generated, instead of specified by hand. When they use the same :name, the SQL produces unexpected results (or, in the best case, breaks because of type errors). In order to guarantee a unique parameter for each instance, I now use UUIDs as names.

Now, I'm not saying that it shouldn't be allowed to share parameters between Raw instances, but
(a) this represents a design choice which I don't know you were aware of, and
(b) it would be good to document this, so users know what to expect

[imnotjames]

Unfortunately, there's only support in query builders (& thus find operators) for query-builder-global variable scopes.

We don't allow for local parameter scopes at this time - and making that change would be a breaking one but we're definitely accepting pull requests that want to explore that.

This is documented here

[bhaveshdaswani93]

I cannot use this change when installing using yarn add typeorm.
What would step to get this change