How work with ALIASES, why row has column name without declared alias?

[nkss7]

Have problem - I build request with join to column that has columns with same name as core table column name. I used aliases, but Row model, has column name without alias prefix(example: Row.toJson(), and 37 values united to 20 values because, we have duplicate names) and how adapter RowMappers do it?

[sky-speed]

@nikita-mtd Have you solved this problem? I'm dealing with the same thing. On the mapping stage io.github.jklingsporn.vertx.jooq.shared.internal.QueryResult has columns without aliases, therefore while calling queryResult.get(COLUMN_NAME) it returns the first occurrence of column in result (Assuming that COLUMN_NAME has more than one occurrence in the result). It's a big problem for columns with common names like: name, latitude, longitude, notes and other similar, commonly used.

cc: @jklingsporn It would be super if you have any ideas how to deal with this problem. Spent many hours of searching, investigating without any results.

[sky-speed]

@jklingsporn any updates?

[jklingsporn]

Can you help me understand the issue better? I'm assuming you craft a query with a JOIN in which two or more tables have a column with the same name and even when you provide an alias the result is not part of the QueryResult? What driver are you using?

[sky-speed]

Exactly, I'm executing a query with a JOIN between tables with the same column names. Let's have a look on the simple example:

CREATE TABLE  country (
    id int8,
    lat float8,
    lon float8,
    PRIMARY KEY (id)
);

CREATE TABLE  city (
    id int8,
    lat float8,
    lon float8,
    country_id int8 foreign key,
    PRIMARY KEY (id)
);

jOOQ query:

private static Country ctr = Tables.COUNTRY.as("ctr");
private static City cit = Tables.CITY.as("cit");

io.github.jklingsporn.vertx.jooq.classic.reactivepg.ReactiveClassicGenericQueryExecutor queryExecutor;

queryExecutor
    .query(dslContext -> dslContext
        .select()
        .from(cit).join(ctr).on(cit.COUNTRY_ID.eq(ctr.ID))
        .getQuery())
    .map(queryResults ->
       queryResults.stream()
            .map(queryResult -> {
                // values in query result don't contain table context, only name of column like: "id", "lat", "lon", "lat", "lon", etc.
                queryResult.get(cit.ID)   // returns country id
                queryResult.get(cit.LAT)    // returns country lat
                queryResult.get(cit.LON)    // returns country lon
                queryResult.get(ctr.ID)    // returns country id
                queryResult.get(ctr.LAT)    // returns country lat
                queryResult.get(ctr.LON)    // return country lat
            }).collect(toList()
    )

Context:

• DB - postgres version 11. Driver version: 42.2.6

Libs:

• vert.x: 3.9.9
• io.github.jklingsporn:vertx-jooq-classic-reactive:jar:5.1.1
• io.github.jklingsporn:vertx-jooq-generate:jar:5.1.1

[jklingsporn]

Have you tried to work with aliases for the columns directly? See below:

private static Country ctr = Tables.COUNTRY.as("ctr");
private static City cit = Tables.CITY.as("cit");

io.github.jklingsporn.vertx.jooq.classic.reactivepg.ReactiveClassicGenericQueryExecutor queryExecutor;
//assuming lat is an integer, idk
Field<Integer> latCountry = ctr.lat.as("lat_country"); 
Field<Integer> latCity = cit.lat.as("lat_city"); 
//insert all fields you need to select

queryExecutor
    .query(dslContext -> dslContext
/*select all fields directly*/
        .select(latCountry, latCity /*INSERT OTHER FIELDS*/)
        .from(cit).join(ctr).on(cit.COUNTRY_ID.eq(ctr.ID))
        .getQuery())
    .map(queryResults ->
       queryResults.stream()
            .map(queryResult -> {
                // values in query result don't contain table context, only name of column like: "id", "lat", "lon", "lat", "lon", etc.
                queryResult.get(idCity)   // returns city id
                queryResult.get(latCity)    // returns city lat
                queryResult.get(lonCity)    // returns city lon
                queryResult.get(idCountry)    // returns country id
                queryResult.get(latCountry)    // returns country lat
                queryResult.get(lonCountry)    // return country lat
            }).collect(toList()
    )

[sky-speed]

Yes, I've tried:

Field<Integer> latCountry = ctr.lat.as("lat_country"); 
Field<Integer> latCity = cit.lat.as("lat_city"); 

.select(latCountry, latCity, asterisk())

It will work, but in case of returning a lot of columns, I need to add a lot of boilerplate like that. Do you have any other ideas? The best would be to enhance queryResult with aliases defined on the table.