CODEWITHSUNDEEP
spring-bootkotlinspring-datadsljooq
Nikita Namestnikov
Nikita Namestnikov

Reputation: 344

How to check record is fully empty on left join in jooq query

I try to fetch a record from table with left join on another table. An information in the second table can be not found but I expect an information from the first table.

    val citizenship = Tables.COUNTRIES.`as`("citizenship")
    try {
        return context.selectFrom(Tables.CLIENT_PROJECTIONS
                .leftJoin(citizenship).on(
                        Tables.CLIENT_PROJECTIONS.CITIZENSHIP_COUNTRY_CODE.eq(
                                citizenship.CODE_ALPHA2
                        )
                )
        ).where(Tables.CLIENT_PROJECTIONS.ID.eq(id)).fetchOne {
            val clientProjection = ClientProjectionMapper.map(it.into(Tables.CLIENT_PROJECTIONS)) ?: return@fetchOne null
            clientProjection.citizenship = CountryMapper.map(it.into(citizenship))
            clientProjection
        }
    } catch (ex: DataAccessException) {
        logger.error("Failed to access to database", ex)
        throw ex
    }

I convert data from CountriesRecord to Entity in CountryMapper:

object CountryMapper : RecordMapper<CountriesRecord, Country> {
  override fun map(record: CountriesRecord?): Country? = when {
    record != null -> {
        Country(
                countryCode = record.codeAlpha,
                title = record.title
        )
    }
    else -> {
        null
    }
  }
}

But if query returns null in every fields of CountriesRecord my map method receive a non-nullable entity but everyone fields of this entity is empty.

I can check every field of CountriesRecord is it null but i think that isn't good idea. Can I check it by another more best way? May be I should write more correct query to database?

Upvotes: 1

Views: 1290

Answers (1)

Lukas Eder
Lukas Eder

Reputation: 221145

A LEFT JOIN in SQL does exactly that. It produces nulls for all columns of the left joined table for which there was no match in the join's ON clause.

You don't have to check whether each column is null. The primary key will be good enough, because that should have a NOT NULL constraint on it, meaning that if the primary key value is null (record.codeAlpha), then that must be because of the LEFT JOIN.

Change your second mapper to this:

object CountryMapper : RecordMapper<CountriesRecord, Country> {
  override fun map(record: CountriesRecord?): Country? = when {
    record?.codeAlpha != null -> {
      Country(
        countryCode = record.codeAlpha,
        title = record.title
      )
    }
    else -> {
      null
    }
  }
}

Upvotes: 1

Related Questions