CODEWITHSUNDEEP
sqlruby-on-railspostgresql
y.bregey
y.bregey

Reputation: 1509

Multiple records as result of inner join on array values

I have two tables in Postgres:

events (id, occurrence_dates) # occurrence_dates is array
dates_calendar (date)

When events.occurrence_dates consists of a few equal values, {"2017-11-08 00:00:00","2017-11-08 00:00:00"} 2 values as example, following query gives a single record:

SELECT "events".* 
FROM "events" 
   INNER JOIN dates_calendar dc ON dc.date = ALL(occurrence_dates)

How to get as many records as length of events.occurrence_dates?

UPD: i'm using Ruby on Rails, but question is provided in SQL context

Rails scope based on @michel.milezzi answer:

scope :all_events, -> do
  select("events_sb.*").from(<<-SQL.squish)
    dates_calendar dc
    INNER JOIN (SELECT *, UNNEST(occurrence_dates) oc_date FROM events) AS events_sb
    ON (events_sb.oc_date = dc.date)
  SQL
end

Upvotes: 0

Views: 435

Answers (2)

Michel Milezzi
Michel Milezzi

Reputation: 11155

You can use UNNEST to expand the array and then use a regular join:

-- CTE with test data
WITH dates_calendar(date) AS (
    VALUES 
        ('2017-11-08 00:00:00'::TIMESTAMP), 
        ('2017-11-09 00:00:00'), 
        ('2017-11-10 00:00:00')
), events (id, occurrence_dates) AS (
    VALUES
        (1, '{"2017-11-08 00:00:00", "2017-11-08 00:00:00","2017-11-09 00:00:00","2017-11-10 00:00:00"}'::TIMESTAMP[]), 
        (2, '{"2017-11-08 00:00:00","2017-11-09 00:00:00"}'), 
        (3, '{"2017-11-08 00:00:00"}')
), events_sb AS (
    SELECT id, UNNEST(occurrence_dates) oc_date FROM events
)
SELECT 
    events_sb.* 
FROM 
    dates_calendar dc JOIN events_sb ON (events_sb.oc_date = dc.date) ORDER BY id;

--CTE
WITH events_sb AS (
    SELECT id, UNNEST(occurrence_dates) oc_date FROM events
)
SELECT 
    events_sb.* 
FROM 
    dates_calendar dc JOIN events_sb ON (events_sb.oc_date = dc.date) ORDER BY id;    

--SUBQUERY
SELECT 
    events_sb.* 
FROM 
    dates_calendar dc JOIN (SELECT id, UNNEST(occurrence_dates) oc_date FROM events) AS events_sb ON (events_sb.oc_date = dc.date) ORDER BY id;

To call it in Rails you must do a plain sql, such explained here and here.

Upvotes: 2

Breathe
Breathe

Reputation: 724

Is occurrence_dates a json data type? If yes, you could simply use the json_populate_recordset() function.

Upvotes: 0

Related Questions