Adding a custom field/column with a value in a slice

[sillen102]

Hi

I want to add a custom field when doing a complex join with a value
Basically something like this:

SELECT p.first_name, "Smith" AS "last_name" 
FROM person p
WHERE first_name = "John"

In kotlin that would be something like this:

PersonTable
  .slice(PersonTable.first_name, "" HERE IS THE PROBLEM "")
  .select(PersonTable.first_name eq "John")

The field "last_name" doesn't exist in any table in this example. I want to add it to the ResultRow.

My real query is much more complex but I can't share it publically.
Basically what I want is to add the last_name column to every ResultRow I would get out of the query with the value "Smith". And then a way to access that column later.

Can this be done in JetBrains Exposed?

This is what I have tried unsuccessfully:

PersonTable
  .slice(PersonTable.first_name, stringLiteral("Smith").alias("last_name"))
  .select(PersonTable.first_name eq "John")

The above doesn't work the way I want it to. It does add a column that has the value "Smith" but the column name/alias is 'Smith'last_name instead of just last_name. The reason why this doesn't work is that I will be doing a union with another select that will have a different value for last_name and therefore I need the column name to be the same in both those selects.

[Tapac]

Can you share the sample with union that doesn't work? afaik, the only first column name in union is matter the other should only be in the same order.

[sillen102]

The problem isn't the union part. The problem is that the value I want to set is being added to the column name that is being created. Which means that if I were to try creating the same column with another value in the next select I would not be able to do that.

I don't actually need to do a UNION. I'm not sure I will. I will be getting 5 or 6 things where I need to have the same column name but with different values for each entity. And then I will be looping over them and mapping to a list of another entity where these will be values on a field. That's why I want them to have the SAME NAME for that column. That would make my code clean and I don't have to treat anything differently. The values I'm putting are coming from an enum btw (if that matters somehow).

For instance if I were to try and do a second query where I want to add the last name Johnson to everyone named Peter I would get a column named 'Johnson'last_name

What I want is to be able to do something like this:

SELECT p.first_name, "Smith" AS "last_name" 
FROM person p
WHERE first_name = "John"
UNION ALL
SELECT p.first_name, "Johnson" AS "last_name"
FROM person p
WHERE first_name = "Peter"
ORDER BY first_name;

The problem is that if the column isn't named 'last_name' I can't do what I want. I will get the column name 'Smith'last_name from the first one and 'Johnson'last_name from the second. I want both to "create" a column with the same name. How can I do that?

Basically what I'm getting is this:

SELECT p.first_name, "Smith" "last_name"
FROM person p
WHERE first_name = "John"

I actually ran the query in a terminal without the "AS" and got the same result. How do I add the "AS" keyword is basically my question.

[sillen102]

With regards what I'm trying to do. Am I using the correct approach? Is there another way I would be able to obtain the same result?

Is there a way for me to run a raw query string for example?

[Tapac]

Afaik, AS is an optional keyword when you use the alias for an expression, so everything should works fine.

You still can execute "raw" SQL as described in wiki

[sillen102]

Ok I see. Is there a way to access the information in the RowResult using the alias only?

Let's say if I were to do something like this:

object PartyTable : LongIdTable("party_table"), ISqlExpressionBuilder {
    val date = date("date")
}

object WeddingTable : LongIdTable("wedding_table"), ISqlExpressionBuilder {
    val date = date("date")
}

enum class EventType() {
    PARTY,
    WEDDING
}

class Event(
    val date: LocalDate,
    val type: EventType
)

val eventsRows = PartyTable
    .slice(PartyTable.date.alias("date"),
        stringLiteral(EventType.PARTY.name).alias("event_type"))
    .select(PartyTable.date.between(LocalDate.parse("2021-01-01"), LocalDate.now())
    .union(
        WeddingTable
            .slice(WeddingTable.date.alias("date"),
                stringLiteral(EventType.WEDDING.name).alias("event_type"))
            .select(WeddingTable.date.between(LocalDate.parse("2021-01-01"), LocalDate.now()))
    ).toList()

var eventList = mutableListOf<Event>()

for (row in eventsRows) {
    eventList.add(
        Event(
            date = row["access the date using alias only?"],
            type = row["access the type using alias only?"]
        )
    )
}

Is it possible to access the data in a ResultRow using alias only? Because using the expression would basically mean I would end up with a whole bunch of possible cases.

I could probably do a map() to the Event entity right after the select(). But I don't want to do that in this class. I want to be able do it separately somewhere else (in a mapper class).

[sillen102]

Also is there a way to do a UNION ALL?

[Tapac]

As the only columns from the first select in a union will be available in the result set you can store it in a variable and use it to get data from ResultRow. I'm not sure but I think that the code below also will work (you can use aliases if you want too):

val eventType = stringLiteral(EventType.PARTY.name)
val eventList = PartyTable
    .slice(PartyTable.date, eventType)
    .select(PartyTable.date.between(LocalDate.parse("2021-01-01"), LocalDate.now())
    .unionAll(
        WeddingTable
            .slice(WeddingTable.date, stringLiteral(EventType.WEDDING.name))
            .select(WeddingTable.date.between(LocalDate.parse("2021-01-01"), LocalDate.now()))
    ).map {
        Event(
               date = row[PartyTable.date],
               type = row[eventType ]
        )
    }

[bog-walk]

Please consider reopening this issue on YouTrack if the problem requires more answers.