Does anyone happen to know how I can do an `upsert...

# exposed

[Andres]

Does anyone happen to know how I can do an upsert that would append a value to an existing array column? I am trying to do a bulk update.

[Andres]

This is the best I have so far...

override suspend fun addEngagementNotification(userIds: List<String>, engagementNotification: String) {
    newSuspendedTransaction(<http://Dispatchers.IO|Dispatchers.IO>) {
        // Fetch current engagement notifications for the users
        val existingEngagements = Userengagements
            .select(Userengagements.userId, Userengagements.engagementNotifications)
            .where { Userengagements.userId inList userIds }

        // Iterate through the userIds and prepare the update for batchUpsert
        Userengagements.batchUpsert(
            userIds,
            onUpdateExclude = listOf(Userengagements.hasAvatar, Userengagements.hasOnboarded, Userengagements.hasReviewRequested),
        ) { userId ->
            // Check if the user already has engagement notifications
            val currentNotifications = existingEngagements
                .firstOrNull { it[Userengagements.userId] == userId }
                ?.get(Userengagements.engagementNotifications)
                ?: emptyList()

            // Append the new notification or create a new list
            val updatedNotifications = currentNotifications + engagementNotification

            // Set the updated list of engagement notifications
            this[Userengagements.userId] = userId
            this[Userengagements.engagementNotifications] = updatedNotifications
        }
    }
}

[Chantal Loncle]

Hi @Andres Have you considered using an SQL function to perform the conditional append, instead of querying for every userId? array_append() seems like it might be a fit for your use case (or array_prepend() depending on where you want the value positioned).

Here's an example setup (tested on PostgreSQL):

// define a custom function class
class ArrayAppend<E, T : List<E>?>(
    val expression: Expression<T>,
    val element: E,
    columnType: IColumnType<T & Any>
) : Function<T>(columnType) {
    override fun toQueryBuilder(queryBuilder: QueryBuilder) {
        // or use PostgreSQL || operator
        queryBuilder {
            append("array_append(")
            append(expression, ",", element.toString())
            append(")")
        }
    }
}

// define a custom column extension
fun <E, T : List<E>?> ExpressionWithColumnType<T>.append(
    element: E
): ArrayAppend<E, T> =
    ArrayAppend(this, element, this.columnType)

Here's a very basic example that uses the above:

object Tester : IntIdTable("tester") {
    val numbers = array<Int>("numbers")
}

val data = listOf(listOf(11, 22, 33), emptyList(), listOf(99))
val ids = Tester.batchInsert(data) { nums ->
    this[Tester.numbers] = nums
}.map { it[Tester.id] }

// selectAll() outputs
// (1, [11, 22, 33])
// (2, [])
// (3, [99])

val newNumber = 77
Tester.batchUpsert(
    data = ids,
    onUpdate = {
        it[Tester.numbers] = Tester.numbers.append(newNumber)
    }
) { id ->
    this[Tester.id] = id
    this[Tester.numbers] = listOf(newNumber)
}

// selectAll() outputs
// (1, [11, 22, 33, 77])
// (2, [77])
// (3, [99, 77])

And if you needed the new value also to be parameterized in the generated SQL, you could instead do something like this:

class ArrayAppend<E, T : List<E>?>(
    expression: ExpressionWithColumnType<T>,
    element: Expression<E>
) : CustomFunction<T>(
    functionName = "array_append",
    columnType = expression.columnType,
    expr = arrayOf(expression, element)
)

inline fun <reified E, T : List<E>?> ExpressionWithColumnType<T>.append(
    element: E
): ArrayAppend<E, T> {
    val elementParam = when (E::class) {
        Int::class -> intParam(element as Int)
        String::class -> stringParam(element as String)
        else -> error("Unknown element found")
    } as Expression<E>
    return ArrayAppend(this, elementParam)
}

[Andres]

Hi @Chantal Loncle thanks for responding this worked great!