funny.sql

SELECT alerts.*,
       'alert' AS type,
       (
         SELECT COUNT(recommendations.id)
         FROM recommendations
         LEFT JOIN recommendations_eav
           ON recommendations.id = recommendations_eav.recommendation AND
           (
             CASE WHEN 'c5d7d53e-5cb0-11e5-a1b3-f23c91c841bd'::uuid IS NOT NULL THEN recommendations_eav."user" = 'c5d7d53e-5cb0-11e5-a1b3-f23c91c841bd' ELSE FALSE END
           ) AND
           recommendations_eav.action = 'Read'
         WHERE recommendations_eav.id IS NULL AND
               recommendations.deleted_at IS NULL AND
               recommendations.hidden IS FALSE AND
               COALESCE(ARRAY_LENGTH(recommendations.referring_objects, 1), 0) > 0 AND
               ARRAY[alerts.id] <@ recommendations.referring_objects
       ) AS new_recommendations,
       (
         SELECT url FROM photos
         INNER JOIN recommendations
           ON photos.listing_mui = recommendations.matrix_unique_id
         WHERE ARRAY[alerts.id] <@ recommendations.referring_objects
         ORDER BY recommendations.updated_at DESC
         LIMIT 1
       ) as cover_image_url,
       ST_AsGeoJSON(points) AS points,
       EXTRACT(EPOCH FROM created_at) AS created_at,
       EXTRACT(EPOCH FROM updated_at) AS updated_at,
       EXTRACT(EPOCH FROM deleted_at) AS deleted_at,
       EXTRACT(EPOCH FROM minimum_sold_date) AS minimum_sold_date,
       property_types::text[] AS property_types,
       property_subtypes::text[] AS property_subtypes,
       listing_statuses::text[] AS listing_statuses,
       excluded_listing_ids::uuid[] AS excluded_listing_ids
FROM alerts
JOIN unnest(ARRAY['7187e0cb-6284-4ba9-b9b8-4100a671bb93',
'd2665522-8bca-4c0e-bef0-4bfd9e750b0d',
'df1661a3-33c1-49d6-8644-7fbc223322a3',
'c7b30d54-54f5-44c0-a556-ad826d04b173',
'0e13a38e-5d13-4d09-970e-33bab3457d46',
'26e431bb-f5df-45d8-8a7b-2768e09f73fd',
'0c98d02c-7f4f-4b56-bbc7-7de0a640ef62',
'3c0716ec-4976-44ba-8f28-b24b0b329f5a',
'db58dcb2-58d5-40ec-b14b-1609a0bc2041',
'c1b8b4bd-f5aa-4280-8155-d427c4b7492a']::uuid[]) WITH ORDINALITY t(aid, ord) ON alerts.id = aid
ORDER BY t.ord