CODEWITHSUNDEEP
sqlknex.js
nicholaswmin
nicholaswmin

Reputation: 22949

Selecting only distinct rows based on a column in Knex

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.

Upvotes: 3

Views: 8317

Answers (2)

martin.macko.47
martin.macko.47

Reputation: 897

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.

Upvotes: 3

Juan Carlos Oropeza
Juan Carlos Oropeza

Reputation: 48197

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")

Upvotes: 1

Related Questions