Selecting only distinct rows based on a column in Knex

Question

I'm using Knex, a pretty nice SQL builder.

I've got a table called Foo which has 3 columns

+--------------+-----------------+
| id           | PK              |
+--------------+-----------------+
| idFoo        | FK (not unique) |
+--------------+-----------------+
| serialNumber | Number          |
+--------------+-----------------+

I'd like to select all rows with idFoo IN (1, 2, 3).

However I'd like to avoid duplicate records based on the same idFoo.

Since that column is not unique there could be many rows with the same idFoo.

A possible solution

My query above will of course return all with idFoo IN (1, 2, 3), even duplicates.

db.select(
  "id",
  "idFoo",
  "age"
)
.from("foo")
.whereIn("idFoo", [1, 2, 3])

However this will return results with duplicated idFoo's like so:

+----+-------+--------------+
| id | idFoo | serialNumber |
+----+-------+--------------+
| 1  | 2     | 56454        |
+----+-------+--------------+
| 2  | 3     | 75757        |
+----+-------+--------------+
| 3  | 3     | 00909        |
+----+-------+--------------+
| 4  | 1     | 64421        |
+----+-------+--------------+

What I need is this:

+----+-------+--------------+
| id | idFoo | serialNumber |
+----+-------+--------------+
| 1  | 2     | 56454        |
+----+-------+--------------+
| 3  | 3     | 00909        |
+----+-------+--------------+
| 4  | 1     | 64421        |
+----+-------+--------------+

I can take the result and use Javascript to filter out the duplicates. I'd specifically like to avoid that and write this in Knex.

The question is how can I do this with Knex code?

I know it can be done with plain SQL (perhaps something using GROUP BY) but I'd specifically like to achieve this in "pure" knex without using raw SQL.

Answer 1

Knex.js supports groupBy natively. You can write:

knex('foo').whereIn('id',
  knex('foo').max('id').groupBy('idFoo')
)

Which is rewritten to the following SQL:

SELECT * FROM foo
  WHERE id IN (
    SELECT max(id) FROM foo
      GROUP BY idFoo
  )

Note that you need to use the subselect to make sure you won't mix values from diffrent rows within the same group.