1

How would I do something like this:

SELECT * 
from notifications 
WHERE read=false AS unread_notifications
  and WHERE read=true 
  AND deleted=false AS read_notifications;

Result Example:

unread_notifications : [
  { id: ...,
    read: false,
    deleted: false,
    subscriber: ...,
    messageDate: ...,
    message: ...
  },
  ...etc  
];

read_notifications : [
  { id: ...,
    read: true,
    deleted: false,
    subscriber: ...,
    messageDate: ...,
    message: ...
  },
  ...etc  
];

In other words, I want to combine two queries into one:

SELECT * FROM notifications WHERE read=false LIMIT 99;
SELECT * FROM notifications WHERE read=true AND deleted=false LIMIT 99;

Example:

CREATE TABLE notifications (
  id VARCHAR(36) DEFAULT uuid_generate_v1mc(),
  read BOOLEAN DEFAULT false,
  deleted BOOLEAN DEFAULT false,
  subscriber VARCHAR NOT NULL,
  messageDate TEXT DEFAULT TO_CHAR(NOW(), 'Mon DD, YYYY HH24:MI:SS'),
  message TEXT
);

INSERT INTO notifications (subscriber, message, read)
  VALUES
  ('Sherry Waters', 'has been added to the Carlotta Corp gateway.', false),
  ('Carl Sagan', 'has cancelled his membership to the Carlotta Prime plan.', false),
  ('Parker Posey', 'is late to pay for the Carlotta Prime plan.', false),
  ('Bob Aronssen', 'has been succesfully charged for the Carlotta Prime membership!', false),
  ('Axle Root', 'has been suspended due to non-payment', true),
  ('Shaniqua Smith', 'has been succesfully charged for the Carlotta Primer membership!', true),
  ('Adam Vicks', 'has parked his membership and is now an inactive subscriber', true);
  • 1
    Do you want the result as a JSON? To combine two queries, use UNION ALL – a_horse_with_no_name Mar 27 '18 at 18:23
  • Yeah, I'll be mapping over it on the front-end to display it. – Matt Carlotta Mar 27 '18 at 18:30
  • 1
    personally, I would prefer WHERE read AND NOT deleted. – Lennart Mar 12 '19 at 9:54
1

UPDATE 9/11/2018: Here's a more concise query:

SELECT * FROM (
    SELECT json_agg(x) as unreadNotifications
    FROM (SELECT * FROM notifications WHERE READ = false ORDER BY key DESC LIMIT 99) as x
) x
CROSS JOIN (
    SELECT json_agg(y) as readNotifications
    FROM (SELECT * FROM notifications WHERE READ = true ORDER BY key DESC LIMIT 99) as y
) y

Result:

[ anonymous { 
   unreadnotifications: [ [Object], [Object], [Object], [Object] ], 
   readnotifications: [ [Object], [Object], [Object] 
  } 
]

Thanks to @Shaulinator, I was able to come up with something that closely resembles what I want:

(SELECT array_to_json(array_agg(row_to_json(x)))    
from ((SELECT * FROM notifications WHERE READ = false LIMIT 99)) x)

UNION ALL

SELECT array_to_json(array_agg(row_to_json(y)))    
from ((SELECT * FROM notifications WHERE READ = true AND deleted = false LIMIT 99)) y;

Result:

 anonymous {
   [0] array_to_json: [ [Object], [Object], [Object], [Object] ] }, // unread notifications
   [1] array_to_json: [ [Object], [Object], [Object] ] } // read notifications
 }

dbfiddle

| improve this answer | |
1

This query will return both queries as a single result. The last column added QueryIndicator is there to indicate which query the results came from. Note that UNION ALL allows duplicates, use UNION if you need to filter them out.

(SELECT
    id
    ,key
    ,READ
    ,deleted
    ,subscriber
    ,messageDate
    ,message
    ,'Unread' AS QueryIndicator
FROM notifications
WHERE READ = false LIMIT 99)

UNION ALL

SELECT
    id
    ,key
    ,READ
    ,deleted
    ,subscriber
    ,messageDate
    ,message
    ,'Read' AS QueryIndicator
FROM notifications
WHERE READ = true
    AND deleted = false LIMIT 99;

Here would be a JSON export example. Based on code found by Jack Christensen on hashrocket.

DB Fiddle Results and Example

select row_to_json(t)
from (
  select (
      select array_to_json(array_agg(row_to_json(d)))
      from (
SELECT
    id
    ,key
    ,READ
    ,deleted
    ,subscriber
    ,messageDate
    ,message
FROM notifications 
WHERE READ = true 
    AND deleted = false LIMIT 99
     ) d
    ) as Read_Notifications
    FROM notifications
WHERE READ = true
    AND deleted = false LIMIT 1)t
    union all
    select row_to_json(t)
from (
  select (
      select array_to_json(array_agg(row_to_json(c)))
      from (
SELECT
    id
    ,key
    ,READ
    ,deleted
    ,subscriber
    ,messageDate
    ,message
FROM notifications 
WHERE READ = false   LIMIT 99
     ) c
    ) as Unread_Notifications
    FROM notifications
WHERE READ = false
 LIMIT 1)t;
| improve this answer | |
  • Getting a syntax error at "UNION": db-fiddle.com/f/u8hsZBD9UGHeegoVFiVF7a/1 – Matt Carlotta Mar 27 '18 at 19:06
  • @MFC88 I missed some from syntax there and needed to wrap the top query in quotes to allow limit 99 records. Also added Key column, try it now, ran for me in fiddle. – Shaulinator Mar 27 '18 at 19:12
  • While both examples work, they don't differentiate like the "Result Example" in my post above. – Matt Carlotta Mar 27 '18 at 19:24
  • @MFC88 I believe it is very close to what you requested after my edit. – Shaulinator Mar 27 '18 at 19:51
  • 1
    It's very close, but I think it is a bit unnecessarily nested (anonymous[0].row-to-json.read_notifications, anonymous[1].row_to_json.unread_notifications). Using your other code, I came up with an answer that is a bit less cluttered. Anyway, thanks for the help! – Matt Carlotta Mar 27 '18 at 20:23

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.