Skip to main content

distinct

The distinct method applies a SELECT DISTINCT ON, keeping one row per value of the given column:

const users = await User.distinct('name').all()

select distinct on ("users"."name") "users".* from "users"

Distinct on the primary key, or clearing distinct​

Calling distinct() with no argument, or distinct(true), distincts on the primary key, which dedupes the rows a join fans out — each record comes back once however many joined rows matched it. distinct(false) clears a distinct applied earlier in the chain:

await Host.innerJoin('places').distinct().all()  // each host once, however many places it has
await Host.innerJoin('places').distinct(true).all()  // same as distinct()
await Host.query().distinct('familyName').distinct(false).all()  // distinct cleared

Chaining​

Similar to other execution methods, distinct is chainable to other query-building methods, such as where:

const count = await User.innerJoin('pets as p', { name: 'Aster' })
  .distinct('p.name')
  .count()

select count(DISTINCT $1) as "tablecount" from "users"
inner join "pets" as "p" on "users"."id" = "p"."user_id"