Conditionally use set-retuning function as join clause-CodePudding

Context:

I'm implementing a "job posting" feature that will list jobs available for users. A job can be set for either an interval start_date and end_date or sparse dates in an array service_dates. The availability check should be made using the previously described date information present at the jobs model against an availabilities model.

Their structures are the following

Jobs

        Column          |             Type                               
------------------------- ------------------------------
 id                      | bigint                        
 service_dates           | timestamp without time zone[]
 start_date              | timestamp without time zone   
 end_date                | timestamp without time zone

Availabilities

        Column          |             Type                               
------------------------- ------------------------------
 id                      | bigint                        
 date                    | date
 spaces_left             | integer

When joining then, I wanna cross the job's dates with the matching dates that exist in the availabilities table. The problem is that the job's dates are available in two different ways.

For sparse dates I can have it working if I do the following left join:

SELECT j.*
FROM jobs j
LEFT JOIN 
    availabilities a 
        ON a.user_id = 1234
        AND a.date in (
          --this will compose a set of the sparse dates
          SELECT UNNEST(j.service_dates)
        )

For the start_date and end_date option this will do the trick:

SELECT j.*
FROM jobs j
LEFT JOIN 
    availabilities a 
        ON a.user_id = 1234
        AND a.date in (
          --this will compose a set with the date range between the two dates
          SELECT GENERATE_SERIES(j.start_date::date, j.end_date::date, '1 day'::interval)
        )

My problem is that I need to apply either one or another based on the condition of jobs.service_dates being present for the particular job record. Originally I thought I could do the following:

SELECT j.*
FROM jobs j
LEFT JOIN 
    availabilities a 
        ON a.user_id = 1234
        AND a.date in (
          -- use one or the other based on whether or not there's something on j.service_dates
          CASE cardinality(j.service_dates) > 0
          WHEN TRUE THEN
             (SELECT UNNEST(j.services))
          ELSE
            (SELECT GENERATE_SERIES(j.start_date::date, j.end_date::date, '1) day'::interval)
          END
        )

But I get the following error: ERROR: more than one row returned by a subquery used as an expression

if I try to select from whatever would be returned by the CASE statement there I get the follwing: ERROR: set-returning functions are not allowed in CASE

I think I have what I need in terms of how to logically relate the tables within the scenarios that can be presented already figured out. What I need is a way to conditionally apply either logic.

CodePudding user response：

This table structure is invalid or incomplete (no user_id field in availabilities table) but just to show an idea


SELECT j.*
FROM jobs j
LEFT JOIN 
    availabilities a 
        ON a.user_id = 1234
WHERE 
   case when cardinality(j.service_dates) > 0
     then a.date = any(j.service_dates)
     else a.date between start_date and end_date
   end

or, if generate series is required

SELECT j.*
FROM jobs j
LEFT JOIN 
    availabilities a 
        ON a.user_id = 1234
WHERE 
   case when cardinality(j.service_dates) > 0
     then a.date = any(j.service_dates)
     else a.date = any ( select GENERATE_SERIES(j.start_date::date, j.end_date::date, '1 day'::interval))
   end

CodePudding user response：

How about an (A && !B) || (!A && B) - this is basically what you are trying to achieve. Either the one or the other.

I'd try to translate this to something along the lines:

SELECT j.*
FROM jobs j
LEFT JOIN 
    availabilities a 
        ON a.user_id = 1234
        AND
        (
          (
            a.date in (SELECT UNNEST(j.service_dates)
            AND (cardinality(j.service_dates) = 0)
          )
          OR
          (
            a.date in (SELECT GENERATE_SERIES(j.start_date::date, j.end_date::date, '1 day'::interval)
            AND NOT (cardinality(j.service_dates) = 0) 
          )
        )

There might be syntactic errors. I am not proficient and inspired myself from what you wrote, but I hope you get the idea.