Add support of passing function to Where

[Aracturat]

@IRCraziestTaxi Using ts compiler api we can create LINQ to SQL in functional style (example of using ts compiler api -http://blog.scottlogic.com/2017/05/02/typescript-compiler-api-revisited.html)

For example, instead of this._userRepository.getOne().where(u => u.email).equal(email); we can write this._userRepository.getOne().where(u => u.email == email)

Example of code (js, but available also in ts)

const ts = require('typescript');
function visitAll(node) {
    ts.forEachChild(node, 
    e => {
        console.log(e);
        visitAll(e);
    });
}

function Where(func) {
    const sourceFile = ts.createSourceFile("test.ts", func.toString(), ts.ScriptTarget.ES2016, false);
    visitAll(sourceFile);    
}

Where(e => e.Name > name);

Wrapper for ts AST https://github.com/dsherret/ts-simple-ast Example of simple linter that uses ts API https://github.com/Microsoft/TypeScript/wiki/Using-the-Compiler-API

Online tool that creates similar AST by JS/TS https://astexplorer.net/

[Aracturat]

@IRCraziestTaxi Simple initial code =)

Translate function (e => (e.Param1 == "3" || e.Param2 >= 4 && e.Param3 <= 2); to string: "(e.Param1 = '3' or e.Param2 >= 4) and e.Param3 <= 2"

import * as ts from 'typescript'

function getArrowFunction(func: Function): ts.ArrowFunction {
    let src = ts.createSourceFile("test.ts", func.toString(), ts.ScriptTarget.ES2016, false);
    var expr = <ts.ExpressionStatement> src.statements[0];
    return <ts.ArrowFunction> expr.expression;    
}

function toSql(expr: ts.Node) {
    switch(expr.kind) {
        case ts.SyntaxKind.PropertyAccessExpression:
            var paExpr = <ts.PropertyAccessExpression> expr;
            var idObject = <ts.Identifier> paExpr.expression;
            return idObject.text + "." + paExpr.name.text;
        case ts.SyntaxKind.ParenthesizedExpression:
            var parExpr = <ts.ParenthesizedExpression> expr;
            return "(" + toSql(parExpr.expression) + ")";
        case ts.SyntaxKind.BinaryExpression:
            var bExpr = <ts.BinaryExpression> expr;
            
            var op = "";
            switch (bExpr.operatorToken.kind) {
                case ts.SyntaxKind.EqualsEqualsToken:
                    op = "=";
                    break;
                case ts.SyntaxKind.GreaterThanToken:
                    op = ">";
                    break;
                case ts.SyntaxKind.GreaterThanEqualsToken:
                    op = ">=";
                    break;
                case ts.SyntaxKind.LessThanToken:
                    op = "<";
                    break;
                case ts.SyntaxKind.LessThanEqualsToken:
                    op = "<=";
                    break;
                case ts.SyntaxKind.AmpersandAmpersandToken:
                    op = "and";
                    break;
                case ts.SyntaxKind.BarBarToken:
                    op = "or";
                    break;
            }
            
            return toSql(bExpr.left) + " " + op + " " + toSql(bExpr.right);
        case ts.SyntaxKind.NumericLiteral:
            var nlExpr = <ts.NumericLiteral> expr;
            return nlExpr.text;
        case ts.SyntaxKind.StringLiteral:
            var slExpr = <ts.StringLiteral> expr;
            return "'" + slExpr.text + "'";        
        default:
            return "[undefined]";
    }
}

var arrFunc = getArrowFunction(e => (e.Param == "3" || e.Param2 >= 4) && e.Param3 <= 2);

console.log(toSql(arrFunc.body));

[Aracturat]

Look at https://github.com/typeorm/typeorm/issues/128 for updates

[MichalLytek]

@Aracturat Great work! Function translation to sql is great itself but it also handle my biggest query builder problem - conditions with parenthesis. TypeORM query builder has that problem as typeorm-linq-repository has.

I keep my fingers crossed and I hope that you will combine your knowledge together and build great functional typeorm query builder. Ping me when you will have an alpha to test 😉

[MichalLytek]

Ok, I have one more reservation about passing function to .where(). Your examples show conditions only on the base entity and I would need to reproduce this kind of query:

this.createQueryBuilder("playlist")
    .leftJoinAndSelect("playlist.owner", "owner")
    .leftJoinAndSelect("playlist.users", "user")
    .where("playlist.id = :id AND (owner.id = :userId OR user.id = :userId)")
    .addParameters({ id, userId: user.id })

Do you think that using ts compiler and ast you will be aple to inject params from variables and multi-joined relation properties condition? Like this:

public findByIdAndUser(id: number, user: User): Promise<Playlist|undefined> {
    return this.getOne()
        .where(playlist => playlist.id === id && (playlist.owner.id === user.id || playlist.user.id === user.id))
        .toPromise();
}

The problem is that playlist entity looks like this:

@Entity()
export class Playlist {

    @PrimaryGeneratedColumn()
    public readonly id: number;

    @ManyToOne(_type => User, { nullable: false })
    public owner: User;

    @JoinTable()
    @ManyToMany(_type => User, user => user.sharedPlaylists)
    public users: User[] = [];
}

So we can't do playlist.user.id === user.id but transforming playlist.users.filter(user=> user.id === user.id). Am I correct?

[Aracturat]

@19majkel94 We can use https://www.npmjs.com/package/flora-sql-parser to work with SQL AST. It support joins and conditions.

For example, this object

[
	{ db: null, table: 't', as: null },
	{
		db: 'a',
		table: 'b',
		as: 'b',
		join: 'INNER JOIN',
		on: {
			type: 'binary_expr',
			operator: '=',
			left: { type: 'column_ref', table: 't', column: 'a' },
			right: { type: 'column_ref', table: 'b', column: 'c' }
		}
	},
	{
		db: null,
		table: 'd',
		as: null,
		join: 'LEFT JOIN',
		on: {
			type: 'binary_expr',
			operator: '=',
			left: { type: 'column_ref', table: 'd', column: 'd' },
			right: { type: 'column_ref', table: 'd', column: 'a' }
		}
	}
]

will be converted to

SELECT * FROM t join a.b b on t.a = b.c left join d on d.d = d.a

[MichalLytek]

So you will be able to translate:

playlist.users.filter(user=> user.id === user.id)

into

`inner join playlist.user u on u.id = ${user.id}`

right?

My misgiving is that you would support only simple conditions like (e => (e.Param == "3" || e.Param2 >= 4) && e.Param3 <= 2) not the ones using language features like filter or lower case, dynamic params from variables or multiple joined relation condition which needs generating inner join. I know that only subset of javascript can be supported but be aware about things like relation and filter functions.

[irissoftworks]

Hey guys,

Congrats! This is a pretty awesome little gem we've got here - makes the typeorm experience so much more enjoyable and future proof.

I think it would be beneficial to talk to the typeorm guys and see if they would want to take it in as a scoped module or something ? I mean, more people should know about this so that development is encouraged because there is a lot of potential here, it's really well designed.

Also, this feature about the where clauses would be really awesome. I think there are definitely questions here about what the full extent of supported operations inside where clause should be, and how unsupported operations (if any) would be disallowed, but there seems to be enough value for some quick investigation ?

I don't have a lot of time, but if you guys need some help on small tasks, even if just writing tests, I have done quite a bit of development with TS simple ast to generate service contracts for my non http services (similar to swagger codegen but without http), so I'm familiar with that.

[IRCraziestTaxi]

Wow, it's hard to believe this project has been going on for so long already. It seems like just a few short months ago is when I first published this thing.

Sorry for never chiming in on this issue. Between work, other side projects, and the precious little time I get to improve this one, I haven't really had time to think much about taking this on, so I just kind of let the discussion continue while I look in every once in a while.

However, I just published a new version (1.0.0-alpha.11) that introduces a lot of crucial functionality that was missing before. I feel like it is at the point now where the next step should be using the AST to make queries truly more "LINQ-like". I don't even believe it would be too hefty of a rewrite - essentially all of the private methods could be used almost as-is. The only things that would change would be the interface and, obviously, using the AST to parse queries rather than building those functions incrementally with the existing methods.

The unfortunate thing is that, right now, I don't have a lot of time to be looking at this, but there is a possibility that I will have a little more time to start taking a crack at it in the near future.

[IRCraziestTaxi]

Update on this: I took @Aracturat's sample code and started messing around with this and, after thinking about it and talking with @jsonpj3wt about it, realized that doing this is going to make typescript a dependency - not just a dev dependency - of this lib. That makes me wary because that is a very heavy dependency to enforce on a project wanting to use this lib.

Anybody have thoughts on this?

[spion-h4]

I think a middle-ground design would be nice.

posts.where(p => p.likes.greaterThan(p.dislikes))

Instead of passing T, you would pass Row<T> to the argument with each property being a Column<Name, T> having a set of operators available. You could also export a list of functions and potentially do projections with

posts.where(p => p.likes.greaterThan(p.dislikes))
 .select(p => Row({likes: p.likes, dislikes: p.dislikes, score: p.likes.minus(p.dislikes)}))`

This would be even nicer than node-sql (examples at http://node-sql-examples.github.io/ ) and a bit closer to LINQ.

The types we wrote for node-sql https://github.com/TokyoFarmer/anydb-sql-2/blob/master/d.ts/anydb-sql.d.ts I think are a decent approximate example of how this might work