Add support for table-valued functions #35

Closed
nyc2 opened this Issue Nov 1, 2011 · 18 comments

Comments

Projects
None yet
2 participants
@nyc2

nyc2 commented Nov 1, 2011

Some database systems provide user defined functions that return table values. Those table values can be joined with other relational objects like tables or views. The main advantage is the possibility to make use of parameters in those user defined functions.

An exemplary implementation of this kind of user defined functions is Microsoft SQL Server's Table-Valued User-Defined Function (see http://msdn.microsoft.com/en-us/library/ms191165.aspx). Querydsl support would be a great benefit.

Maybe, Querydsl's RelationalPath can be extended in a way that supports parameters and allow the generation of SQL like select * from Table t join TableValuedFunction(?, ...) tvf on tvf.value=t.value.

@nyc2

This comment has been minimized.

Show comment
Hide comment
@timowest

This comment has been minimized.

Show comment
Hide comment
@timowest

timowest Nov 3, 2011

Member

Just for clarification. Should I support both

from table join tableValuedFunction where ...

from table join tableValuedFunction alias where ...
Member

timowest commented Nov 3, 2011

Just for clarification. Should I support both

from table join tableValuedFunction where ...

from table join tableValuedFunction alias where ...
@nyc2

This comment has been minimized.

Show comment
Hide comment
@nyc2

nyc2 Nov 4, 2011

To avoid ambiguities concerning column names I'd prefer the latter alternative.

nyc2 commented Nov 4, 2011

To avoid ambiguities concerning column names I'd prefer the latter alternative.

@timowest

This comment has been minimized.

Show comment
Hide comment
@timowest

timowest Nov 4, 2011

Member

I added now support for table valued function calls in joins. I added the RelationalFunctionCall type for this. Here is a usage example :

    SQLQuery query = new SQLQueryImpl(SQLTemplates.DEFAULT);
    QSurvey survey = QSurvey.survey;
    query.from(survey).join(RelationalFunctionCall.create(Survey.class, "functionCall()"), Expressions.path(Survey.class, "fc"));
    query.where(survey.name.isNotNull());
    assertEquals("from SURVEY SURVEY\njoin functionCall() as fc\nwhere SURVEY.NAME is not null", query.toString());

Could you verify that this works for you?

Member

timowest commented Nov 4, 2011

I added now support for table valued function calls in joins. I added the RelationalFunctionCall type for this. Here is a usage example :

    SQLQuery query = new SQLQueryImpl(SQLTemplates.DEFAULT);
    QSurvey survey = QSurvey.survey;
    query.from(survey).join(RelationalFunctionCall.create(Survey.class, "functionCall()"), Expressions.path(Survey.class, "fc"));
    query.where(survey.name.isNotNull());
    assertEquals("from SURVEY SURVEY\njoin functionCall() as fc\nwhere SURVEY.NAME is not null", query.toString());

Could you verify that this works for you?

@nyc2

This comment has been minimized.

Show comment
Hide comment
@nyc2

nyc2 Nov 7, 2011

Could you please provide me with a pre-compiled bundle to allow me to verify this new functionality? Thank you for the quick implementation of this feature.

Where can the parameters be passed to functionCall()? And where to place the join conditions? Which type is Survey.class of?

nyc2 commented Nov 7, 2011

Could you please provide me with a pre-compiled bundle to allow me to verify this new functionality? Thank you for the quick implementation of this feature.

Where can the parameters be passed to functionCall()? And where to place the join conditions? Which type is Survey.class of?

@timowest

This comment has been minimized.

Show comment
Hide comment
@timowest

timowest Nov 7, 2011

Member

I simplified the contract a little bit :

    StringPath str = new StringPath("str");
    RelationalFunctionCall<String> functionCall = RelationalFunctionCall.create(String.class, "getElements", "a", str);
    assertEquals("getElements({0}, {1})", functionCall.getTemplate().toString());
    assertEquals(ConstantImpl.create("a"), functionCall.getArg(0));
    assertEquals(str, functionCall.getArg(1));

The first argument to the create method is the row type, the second the function name and the rest are the arguments. All non-Expression arguments are treated as constants.

Member

timowest commented Nov 7, 2011

I simplified the contract a little bit :

    StringPath str = new StringPath("str");
    RelationalFunctionCall<String> functionCall = RelationalFunctionCall.create(String.class, "getElements", "a", str);
    assertEquals("getElements({0}, {1})", functionCall.getTemplate().toString());
    assertEquals(ConstantImpl.create("a"), functionCall.getArg(0));
    assertEquals(str, functionCall.getArg(1));

The first argument to the create method is the row type, the second the function name and the rest are the arguments. All non-Expression arguments are treated as constants.

@timowest

This comment has been minimized.

Show comment
Hide comment
@timowest

timowest Nov 7, 2011

Member

I just deployed 2.2.4.BUIlD-SNAPSHOT

Member

timowest commented Nov 7, 2011

I just deployed 2.2.4.BUIlD-SNAPSHOT

@timowest

This comment has been minimized.

Show comment
Hide comment
@timowest

timowest Nov 7, 2011

Member

You can add join conditions via on(...) calls

Member

timowest commented Nov 7, 2011

You can add join conditions via on(...) calls

@nyc2

This comment has been minimized.

Show comment
Hide comment
@nyc2

nyc2 Nov 8, 2011

Sorry, I cannot find 2.2.4.BUILD-SNAPSHOT...

nyc2 commented Nov 8, 2011

Sorry, I cannot find 2.2.4.BUILD-SNAPSHOT...

@timowest

This comment has been minimized.

Show comment
Hide comment
@timowest

timowest Nov 8, 2011

Member

Sorry. Should be available now :
https://source.mysema.com/maven2/snapshots/com/mysema/querydsl/querydsl-sql/2.2.4.BUILD-SNAPSHOT/

Timo

On Tue, Nov 8, 2011 at 8:09 AM, nyc2 <
reply@reply.github.com>wrote:

Sorry, I cannot find 2.2.4.BUILD-SNAPSHOT...


Reply to this email directly or view it on GitHub:
#35 (comment)

Timo Westkmper
Mysema Oy
+358 (0)40 591 2172
www.mysema.com

Member

timowest commented Nov 8, 2011

Sorry. Should be available now :
https://source.mysema.com/maven2/snapshots/com/mysema/querydsl/querydsl-sql/2.2.4.BUILD-SNAPSHOT/

Timo

On Tue, Nov 8, 2011 at 8:09 AM, nyc2 <
reply@reply.github.com>wrote:

Sorry, I cannot find 2.2.4.BUILD-SNAPSHOT...


Reply to this email directly or view it on GitHub:
#35 (comment)

Timo Westkmper
Mysema Oy
+358 (0)40 591 2172
www.mysema.com

@nyc2

This comment has been minimized.

Show comment
Hide comment
@nyc2

nyc2 Nov 9, 2011

Obviously, I'm doing something wrong. I really don't know how to build this query:

select tab.col from Table tab join TableValuedFunction('parameter') func on tab.col not like func.col

I tried this:

RelationalFunctionCall<String> func = RelationalFunctionCall.create(String.class, "TableValuedFunction", "parameter");
query().from(QTable).join(func, **Expressions.path(String.class, "func")**).on(QTable.col.like(**how to reference columns returned by TableValuedFunction**).not()).list(QTable.col);

Could you tell me what I have to put in the bold(**)-styled parts to make this working?
At the moment, the join alias seems to be rendered as

func.*

nyc2 commented Nov 9, 2011

Obviously, I'm doing something wrong. I really don't know how to build this query:

select tab.col from Table tab join TableValuedFunction('parameter') func on tab.col not like func.col

I tried this:

RelationalFunctionCall<String> func = RelationalFunctionCall.create(String.class, "TableValuedFunction", "parameter");
query().from(QTable).join(func, **Expressions.path(String.class, "func")**).on(QTable.col.like(**how to reference columns returned by TableValuedFunction**).not()).list(QTable.col);

Could you tell me what I have to put in the bold(**)-styled parts to make this working?
At the moment, the join alias seems to be rendered as

func.*
@nyc2

This comment has been minimized.

Show comment
Hide comment
@nyc2

nyc2 Nov 10, 2011

The problem still remains, that

RelationalFunctionCall<String> tokenizeFunc = RelationalFunctionCall.create(String.class, "tokenize", csvStr);
PathBuilder<String> tokenizeFuncAlias = new PathBuilder<String>(String.class, "tokFunc");
sq().leftJoin(tokenizeFunc, tokenizeFuncAlias).on(QTable.col.like(tokenizeFuncAlias.get(new StringPath("token"))).not());

delivers the following SQL (excerpt):

left join tokenize(?) as tokFunc.*
on not QTable.col like tokFunc.*.token

.* leads to this SQLException:

Caused by: java.sql.SQLException: Incorrect syntax near '.'.

Do you have any suggestions on that?

nyc2 commented Nov 10, 2011

The problem still remains, that

RelationalFunctionCall<String> tokenizeFunc = RelationalFunctionCall.create(String.class, "tokenize", csvStr);
PathBuilder<String> tokenizeFuncAlias = new PathBuilder<String>(String.class, "tokFunc");
sq().leftJoin(tokenizeFunc, tokenizeFuncAlias).on(QTable.col.like(tokenizeFuncAlias.get(new StringPath("token"))).not());

delivers the following SQL (excerpt):

left join tokenize(?) as tokFunc.*
on not QTable.col like tokFunc.*.token

.* leads to this SQLException:

Caused by: java.sql.SQLException: Incorrect syntax near '.'.

Do you have any suggestions on that?

@timowest

This comment has been minimized.

Show comment
Hide comment
@timowest

timowest Nov 10, 2011

Member

I will see what goes wrong later today.

Member

timowest commented Nov 10, 2011

I will see what goes wrong later today.

@timowest

This comment has been minimized.

Show comment
Hide comment
@timowest

timowest Nov 10, 2011

Member

I've had problems to reproduce this issue. This works in Querydsl SQL :

@Test
public void FunctionCall() {
    //select tab.col from Table tab join TableValuedFunction('parameter') func on tab.col not like func.col

    QSurvey table = QSurvey.survey;
    RelationalFunctionCall<String> func = RelationalFunctionCall.create(String.class, "TableValuedFunction", "parameter");
    PathBuilder<String> funcAlias = new PathBuilder<String>(String.class, "tokFunc");
    SQLSubQuery sq = new SQLSubQuery();
    SubQueryExpression<?> expr = sq.from(table)
        .join(func, funcAlias).on(table.name.like(funcAlias.getString("prop")).not()).list(table.name);

    SQLSerializer serializer = new SQLSerializer(new SQLServerTemplates());
    serializer.serialize(expr.getMetadata(), false);
    assertEquals("select SURVEY.NAME\n" +
            "from SURVEY SURVEY\n" +
            "join TableValuedFunction(?) as tokFunc\n" +
            "on not SURVEY.NAME like tokFunc.prop escape '\\'", serializer.toString());

}

Do you use Querydsl SQL with or without JPA?

Member

timowest commented Nov 10, 2011

I've had problems to reproduce this issue. This works in Querydsl SQL :

@Test
public void FunctionCall() {
    //select tab.col from Table tab join TableValuedFunction('parameter') func on tab.col not like func.col

    QSurvey table = QSurvey.survey;
    RelationalFunctionCall<String> func = RelationalFunctionCall.create(String.class, "TableValuedFunction", "parameter");
    PathBuilder<String> funcAlias = new PathBuilder<String>(String.class, "tokFunc");
    SQLSubQuery sq = new SQLSubQuery();
    SubQueryExpression<?> expr = sq.from(table)
        .join(func, funcAlias).on(table.name.like(funcAlias.getString("prop")).not()).list(table.name);

    SQLSerializer serializer = new SQLSerializer(new SQLServerTemplates());
    serializer.serialize(expr.getMetadata(), false);
    assertEquals("select SURVEY.NAME\n" +
            "from SURVEY SURVEY\n" +
            "join TableValuedFunction(?) as tokFunc\n" +
            "on not SURVEY.NAME like tokFunc.prop escape '\\'", serializer.toString());

}

Do you use Querydsl SQL with or without JPA?

@timowest

This comment has been minimized.

Show comment
Hide comment
@timowest

timowest Nov 10, 2011

Member

Ok, this appears to be a bug in Querydsl JPA Native query support. I was always testing on the Querydsl SQL side, that's why I didn't encounter it before.

Member

timowest commented Nov 10, 2011

Ok, this appears to be a bug in Querydsl JPA Native query support. I was always testing on the Querydsl SQL side, that's why I didn't encounter it before.

@timowest

This comment has been minimized.

Show comment
Hide comment
@timowest

timowest Nov 10, 2011

Member

I just deployed a new version.

Member

timowest commented Nov 10, 2011

I just deployed a new version.

@nyc2

This comment has been minimized.

Show comment
Hide comment
@nyc2

nyc2 Nov 11, 2011

This works for me, thanks again!

nyc2 commented Nov 11, 2011

This works for me, thanks again!

@timowest

This comment has been minimized.

Show comment
Hide comment
@timowest

timowest Nov 19, 2011

Member

Released in 2.2.5

Member

timowest commented Nov 19, 2011

Released in 2.2.5

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment