I’m using Exposed, and I’m having trouble retrieving results from a select query..

# ktor

[Tim Malseed]

I’m using Exposed, and I’m having trouble retrieving results from a select query..

[Tim Malseed]

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:

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?

[spand]

Isnt that how count works

[Tim Malseed]

What do you mean?

[spand]

Well that aggregate functions collapse to the group

[spand]

Maybe you want a group by also ?

[Tim Malseed]

For each report, I would like the count of votes for that report, distinct by user id

[Tim Malseed]

But this is a selectAll() function, not a count() function

[spand]

Maybe you should write your sql and we can help translate it to exposed

[Tim Malseed]

Well, yeah that would be a good starting point.

[Tim Malseed]

Unfortunately, that’s half the problem for me as well

[spand]

selectAll just means no sql where clause

[spand]

Try to group by Reports.id

[Tim Malseed]

OK. Does that mean the slice is unnecessary?

[spand]

No. You need to slice all the colums you use in the map

[Tim Malseed]

OK great, that’s all it took

[Tim Malseed]

Can you help me understand - I don’t get why groupBy is necessary, or helps here

[spand]

Try to google sql aggregate functions

[Tim Malseed]

OK, will do. Thanks

[Tim Malseed]

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

[Tim Malseed]

So I don’t know if I’ve made a syntax error, misunderstood the dsl->sql mapping, or misunderstood the sql required!

[spand]

slice is sql select
select is sql where

[Tim Malseed]

slice is sql select

[Tim Malseed]

Hah, thanks so much for this lol

[Tim Malseed]

I couldn’t figure it out

[Kevin Schmeichel]

yeah, Exposed can be pretty confusing. I ended up switching to ktorm which I like much better.

[Kevin Schmeichel]

https://ktorm.liuwj.me/

[Dico]

I too am not a fan of Exposed. I may give ktorm a try.