CODEWITHSUNDEEP
postgresqlknex.js
Vlady Veselinov
Vlady Veselinov

Reputation: 5401

How to select unique?

I'm working on a live music database and I can't think of how to do a query that gets artists that have events on.

I have two tables: artist (containing id, name, description, etc.) and event_artist (many to many relationship) having artist_id and event_id columns.

return knex('event_artist')
  .distinct('artist_id')
  .join('artist', 'event_artist.artist_id', 'artist.id')
  .select('*')
  .offset(offset)
  .limit(howMany)

This returns duplicate ids, which I don't want. How do I fix this?

Upvotes: 0

Views: 739

Answers (2)

coockoo
coockoo

Reputation: 2382

You are looking for a query that selects artists rows that have rows in event_artist columns.

In SQL it can be written using exists

select
  *
from
  artists as a
where
  exists (
    select
      *
    from
      event_artist as ea
    where
      ea.artist_id = a.id
  )

In knex it can be written

knex('artists as a')
  .whereExists(
    knex
      .select('*')
      .from('event_artists as ea')
      .whereRaw('ea.artist_id = a.id')
  )

Upvotes: 1

Fazal Rasel
Fazal Rasel

Reputation: 4526

this should be left join not just Join-

return knex('event_artist')
  .leftJoin('artist', 'event_artist.artist_id', 'artist.id')
  .offset(offset)
  .limit(howMany)

Upvotes: 0

Related Questions