https://kotlinlang.org logo
#ktor
Title
# ktor
t

Tim Malseed

01/17/2020, 1:36 PM
I’m using Exposed, and I’m having trouble retrieving results from a select query..
I’ve got a ‘Reports’ table, a ‘User’ table, and a ‘Votes’ table. Votes is just a join table between user.id and report.id. I’m constructing a query to retrieve all reports, with an additional column representing the sum of their votes:
Copy code
suspend fun getReports(): List<Report> = DatabaseManager.dbQuery {
        val voteCount = Votes.userId.countDistinct()

        (Reports leftJoin Votes)
            .slice(Reports.columns + voteCount)
            .selectAll()
            .map { row ->
                Report(
                    row[Reports.id],
                    row[Reports.userId],
                    row[Reports.title],
                    row[Reports.description],
                    row[voteCount],
                    row[Reports.appVersion],
                    row[Reports.deviceName],
                    row[Reports.osVersion],
                    row[Reports.dateCreated],
                    row[Reports.dateResolved]
                )
            }
    }
But this only ever returns one row. What gives?
s

spand

01/17/2020, 1:48 PM
Isnt that how count works
t

Tim Malseed

01/17/2020, 1:48 PM
What do you mean?
s

spand

01/17/2020, 1:49 PM
Well that aggregate functions collapse to the group
Maybe you want a group by also ?
t

Tim Malseed

01/17/2020, 1:50 PM
For each report, I would like the count of votes for that report, distinct by user id
But this is a selectAll() function, not a count() function
s

spand

01/17/2020, 1:51 PM
Maybe you should write your sql and we can help translate it to exposed
t

Tim Malseed

01/17/2020, 1:52 PM
Well, yeah that would be a good starting point.
Unfortunately, that’s half the problem for me as well
s

spand

01/17/2020, 1:52 PM
selectAll
just means no sql where clause
Try to group by
Reports.id
t

Tim Malseed

01/17/2020, 1:54 PM
OK. Does that mean the slice is unnecessary?
s

spand

01/17/2020, 1:54 PM
No. You need to slice all the colums you use in the map
t

Tim Malseed

01/17/2020, 1:58 PM
OK great, that’s all it took
Can you help me understand - I don’t get why groupBy is necessary, or helps here
s

spand

01/17/2020, 2:03 PM
Try to google sql aggregate functions
t

Tim Malseed

01/17/2020, 2:03 PM
OK, will do. Thanks
I was mostly getting confused by the DSL, because it’s a little hard to translate to sql - and then I’m not that experienced writing complex sql queries, so I can’t validate it at all
So I don’t know if I’ve made a syntax error, misunderstood the dsl->sql mapping, or misunderstood the sql required!
s

spand

01/17/2020, 2:06 PM
slice is sql select select is sql where
t

Tim Malseed

01/17/2020, 2:08 PM
slice is sql select
Hah, thanks so much for this lol
I couldn’t figure it out
k

Kevin Schmeichel

01/17/2020, 5:22 PM
yeah, Exposed can be pretty confusing. I ended up switching to ktorm which I like much better.
👍 1
d

Dico

01/18/2020, 11:24 AM
I too am not a fan of Exposed. I may give ktorm a try.
2 Views