Using a database cast in a subquery using query beans

[PassXml]

Expected behavior

In some database, the comparison of field types is strict and must be explicitly converted. I have two models, Model A has a field of type UUID, and Model B also has a logical primary key (sourceId) stored as varchar. When generating SQL, they cannot be directly compared, and their field types must be converted. So, when using the select method, can I add conversion functions or specify the type? Moreover, when I specify it as a string, it throws the following error.

  new QClass().select("source_id::string")

property [source_id::string] not found on xxxEntity for query - excluding it.

The automatically generated SQL looks like this:

select *
from a
where a.source_id = (select id
                     from b)

select *
from a
where a.source_id = (select id::string
                     from b)

```

[rbygrave]

I think what you are looking to try and do is?

new QClass().alias("b").select("b.source_id::text")

... but the code in the question uses ::string in the subquery and so that doesn't really make sense here ... so there is a good chance I am not following your question and you need to re ask your question more explicitly stating what you are trying to achieve, what you are doing, what it produces in terms of sql.

That is, I think this is a subquery and not the main query. This also means the workaround is to use a string literal for the subquery via inSubQuery() like:

.sourceId.inSubQuery("select id::text from b")

So why the cast to string and not a database type? My guess is you are thinking about the use of ::string in findSingleAttribute() etc where the casting is done on the ORM side rather than on the database side.

For the case of the subquery, the cast MUST be done in sql on the database side, so the cast must be a database cast and not an ebean cast ... so ::text and not ::string should be used.

Now perhaps your question is, why can't the subquery just be: new QClass().select("sourceId::text") ? You did try that and it didn't work?

[PassXml]

I think what you are looking to try and do is?

new QClass().alias("b").select("b.source_id::text")

... but the code in the question uses ::string in the subquery and so that doesn't really make sense here ... so there is a good chance I am not following your question and you need to re ask your question more explicitly stating what you are trying to achieve, what you are doing, what it produces in terms of sql.

That is, I think this is a subquery and not the main query. This also means the workaround is to use a string literal for the subquery via inSubQuery() like:

.sourceId.inSubQuery("select id::text from b")

So why the cast to string and not a database type? My guess is you are thinking about the use of ::string in findSingleAttribute() etc where the casting is done on the ORM side rather than on the database side.

For the case of the subquery, the cast MUST be done in sql on the database side, so the cast must be a database cast and not an ebean cast ... so ::text and not ::string should be used.

Now perhaps your question is, why can't the subquery just be: new QClass().select("sourceId::text") ? You did try that and it didn't work?

In my actual business logic, there are some logic foreign keys, such as using sourceType to distinguish whether they come from table A or table B. This has led to a situation where the sourceId and table Id have inconsistent types. Therefore, I want to invoke the database's type conversion functionality. So, I’m wondering if I can explicitly specify the type in qclass, or add a type conversion function

To be used for converting field types

---

use new QClass().select("sourceId::text") console say

sourceId::text   not found on xxxEntity for query - excluding it.

[rbygrave]

So, I’m wondering if I can explicitly specify the type in qclass, or add a type conversion function

Is this being used in a subquery or in a main query? The difference matters, please show the query that you are actually trying to execute.

It is best if you show us what you are trying to achieve in terms of SQL as well as the query that is being executed. At this stage I suspect you are looking to cast on a subquery but I'm not sure if that is your question yet.

[PassXml]

So, I’m wondering if I can explicitly specify the type in qclass, or add a type conversion function

Is this being used in a subquery or in a main query? The difference matters, please show the query that you are actually trying to execute.

It is best if you show us what you are trying to achieve in terms of SQL as well as the query that is being executed. At this stage I suspect you are looking to cast on a subquery but I'm not sure if that is your question yet.

Yes, used in a subquery

[rbygrave]

Right, so the workaround is to use eqSubQuery() passing the subquery as a string like:

where().eqSubQuery("sourceId", "select id::text from b")

or using query bean .eqSubQuery() passing the subquery as a string

new QA()
  .sourceId.eqSubQuery("select id::text from b")
  .findList();

So currently you are using this workaround right? So you are not blocked per se, but just wondering if you can use a query bean for the query query in this case right?

[PassXml]

Right, so the workaround is to use eqSubQuery() passing the subquery as a string like:

where().eqSubQuery("sourceId", "select id::text from b")

or using query bean .eqSubQuery() passing the subquery as a string

new QA()
  .sourceId.eqSubQuery("select id::text from b")
  .findList();

So currently you are using this workaround right? So you are not blocked per se, but just wondering if you can use a query bean for the query query in this case right?

Yes, either of these two can achieve the effect I want. This is exactly what I want.

[rbygrave]

Cool.

I'm going to reopen this in that yes we have a decent workaround, and the question now is can we address this case of casting when the query is a subquery.

That is, can we get this case to work by allowing a "pass-through" of expressions including casting in the SELECT clause when the query is a subquery. If that is simple enough and easy enough to fix then it would be worth fixing so lets have a look at this case more closely.

[PassXml]

Cool.

I'm going to reopen this in that yes we have a decent workaround, and the question now is can we address this case of casting when the query is a subquery.

That is, can we get this case to work by allowing a "pass-through" of expressions including casting in the SELECT clause when the query is a subquery. If that is simple enough and easy enough to fix then it would be worth fixing so lets have a look at this case more closely.

I tend to favor adding similar conversion functions to the qclass, which can be explicitly called. This way, if the entity changes, it can be checked during the build process, which is one of the biggest advantages compared to native SQL. Then, custom native SQL can be used as a flexible fallback implementation.

However, if the implementation cost here is high, we need to consider whether it is worth it.