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

In normal sql you do it like this.

You perform a self join and try to find a row with same idFoo but bigger id, if you dont find it you have NULL. And will know you are the bigger one.

 SELECT t1.id, t1.idFoo, t1.serialNumber
 FROM foo as t1
 LEFT JOIN foo as t2
   ON t1.id < t2.id
  AND t1.idFoo = t2.idFoo  
 WHERE t2.idFoo IS NULL

So check for left join on knex.js

EDIT:

Just check documentation build this (not tested):

 knex.select('t1.*')
     .from('foo as t1')
     .leftJoin('foo as t2', function() {
        this.on('t1.id', '<', 't2.id')
            .andOn('t1.idFoo ', '=', 't2.idFoo')
        })
     .whereNull("t2.idFoo")