Unable to SELECT COUNT(*) for Korma entity with default fields

Question

I am unable to SELECT COUNT(*) from an entity I have mapped in Korma.

Here is my entity:

(declare users responses) (korma/defentity users (korma/entity-fields :id :slack_id :active :token :token_created) (korma/many-to-many responses :userresponses))

And here is my attempt at a SELECT COUNT(*):

(korma/select schema/users (korma/fields ["count(*)"]) (korma/where {:slack_id slack-id}))

I get this error:

ERROR: column "users.id" must appear in the GROUP BY clause or be used in an aggregate function at character 8 STATEMENT: SELECT "users"."id", "users"."slack_id", "users"."active", "users"."token", "users"."token_created", count(*) FROM "users" WHERE ("users"."slack_id" = $1)

It looks like Korma is including my entity fields even though I'm specifying fields to select in this query. How do I override that?

Answer 1

You can't override it per se. Korma query manipulation functions are always additive, so specifying fields merely specifies additional fields.

To get around this, you can rewrite this query to select against the users table itself rather than the Korma entity users:

(korma/select :users
  (korma/fields ["count(*)"])
  (korma/where {:slack_id slack-id}))

But then you'll have to make do without anything else defined in the users entity.

Alternatively, you could rewrite this entity to not define any entity-fields, then define a wrapped version of this entity with the desired default fields:

(korma/defentity users-raw
  (korma/many-to-many responses :userresponses)))

(def users
  (korma/select
    users-raw
    (korma/fields [:id :slack_id :active :token :token_created])))```

Then you can write your normal queries by adding with/where clauses to this "users" query, and only directly touch users-raw when you need to exclude those fields:

(-> users (with ...) (where ...) (select))