CODEWITHSUNDEEP
kotlinkotlin-exposed
Chris Legge
Chris Legge

Reputation: 789

Select records where id does not equal to x

I have these tables

object BooksAuthors : Table(name = "books_authors") {
    val book = reference("book_id", Books, onDelete = ReferenceOption.CASCADE)
    val author = reference("author_id", Authors, onDelete = ReferenceOption.CASCADE)
}

object Books : IntIdTable() {
    val title = varchar("title", 250)
    val isbn = varchar("isbn", 13)
}

object Authors : IntIdTable() {
    val email = varchar("email", 100).uniqueIndex()
}

i would like to write a query that returns all books that dont have a specific author so i wrote this

suspend fun getBooksWithoutAuthorId(authorId: Int): List<BookDTO> = DbFactory.dbQuery {
    val query = BooksAuthors.innerJoin(Books).select { BooksAuthors.author neq authorId }
    Book.wrapRows(query).map { it.toDTO() }
}

But the query returns books that have the author. What am i doing wrong?

Upvotes: 0

Views: 397

Answers (2)

Михаил Нафталь
Михаил Нафталь

Reputation: 7882

As Sebastian Redl mentioned, there could be books with multiple authors and your query doesn't cover that case.

Correct Exposed query should be:

val query = Books.select {
    Books.id notInSubQuery 
            BooksAuthors.slice(BooksAuthors.book).select { BooksAuthors.author eq authorId }
}

Upvotes: 3

Sebastian Redl
Sebastian Redl

Reputation: 71989

It looks like you have an n:m mapping where a book can have multiple authors.

Your query, as written, finds any book that an author other than your selected one has authored.

This means if authors Alice and Bob wrote a book together, and you want to find books "not by Bob", you would still find the book because Alice took part.

Your desired query cannot be expressed as a simple join; you need nested queries instead.

Something like equivalent to this SQL:

SELECT * from books b WHERE ? NOT IN (
  SELECT ab.author_id FROM authors_books ab WHERE ab.book_id = b.id);

Though I'm afraid I don't know how to express this in Exposed.

https://www.db-fiddle.com/f/7BsVUW95g6L4rXDBCoaXK3/0

Upvotes: 2

Related Questions