Postgresql – Two array_agg at once

arrayoptimizationpostgresql

I'm trying to get all likes/dislikes of multiple post.
The problem can be summarized as:

posts table: id, content, user_id (fk)
users table: id, name
likes table: post_id (fk), user_id (fk), is_liked, is_disliked

To fit my use case, I need to get the list of all likes/dislikes so I've first tried this:

SELECT 
    posts.id, posts.content, users.name as author
    ARRAY(SELECT user_id FROM likes WHERE is_liked and post_id = posts.id) as likes,
    ARRAY(SELECT user_id FROM likes WHERE is_disliked and post_id = posts.id) as dislikes
FROM 
   posts
   INNER JOIN users ON posts.user_id = users.id

This is clearly not optimized. That's why I've tried something with another join on likes table and the array_agg function but I can't figure out how to get two different arrays for likes and dislikes.

Best Answer

That should be simple with array_agg and additional FILTER clauses:

SELECT 
    posts.id, posts.content, users.name as author
    array_agg(likes.user_id) FILTER (WHERE likes.is_liked) as likes,
    array_agg(likes.user_id) FILTER (WHERE likes.is_disliked) as dislikes
FROM posts
   INNER JOIN users ON posts.user_id = users.id
   INNER JOIN likes ON likes.post_id = posts.id
GROUP BY posts.id, posts.content, users.name;

Related Solutions

Mysql – Help optimizing MySQL slow query

I would like to get rid of "Using temporary; Using filesort"

One of the problems I see is that you're using different GROUP BY and ORDER BY clauses. From the manual on how MySQL uses temporary tables:

If there is an ORDER BY clause and a different GROUP BY clause, or if the ORDER BY or GROUP BY contains columns from tables other than the first table in the join queue, a temporary table is created.

As soon as you create a temporary table, it will need to be sorted according to your ORDER BY clause, indicated by 'using filesort'.

This execution plan at leasts uses the indexes to appropriately limit the number of rows found.

I would also look through the docs on ORDER BY optimization.

Postgresql – Social network comments and posts model in PostgreSQL 9.6

First the author table, that's easy.

CREATE TABLE author (
    authorid   serial  PRIMARY KEY,
    username   text    UNIQUE
);

Now we use a self-referential post table to create the hierarchical structure.

CREATE TABLE posts (
    id           serial  PRIMARY KEY,
    parent_post  int     REFERENCES posts,
    authorid     int     NOT NULL REFERENCES author,
    body         text    NOT NULL
);

Test data...

INSERT INTO author (authorid,username) VALUES
  (0, 'ecarroll'),
  (1, 'jbob');
INSERT INTO posts (id,parent_post,authorid,body) VALUES
  (0, null, 0, 'HALLO WORLD'),
  (1 ,0,    1, 'HALLO EVAN' );

Recursive query to get it working.

WITH RECURSIVE t(chain,author_un,text,id) AS (
   SELECT ARRAY[id], username, body, id
   FROM posts
   INNER JOIN author USING (authorid)
   WHERE parent_post IS NULL
   UNION ALL
     SELECT t.chain||p.id, username, p.body, p.id
     FROM t
     INNER JOIN posts AS p
       ON (t.id = p.parent_post)
     INNER JOIN author AS a
       USING (authorid)
)
SELECT *
FROM t;

 chain | author_un |    text     | id 
-------+-----------+-------------+----
 {0}   | ecarroll  | HALLO WORLD |  0
 {0,1} | jbob      | HALLO EVAN  |  1
(2 rows)

For more information on this method, see my post here

How to build a table for a private messaging system that supports replies?

Best Answer

Related Solutions

Mysql – Help optimizing MySQL slow query

Postgresql – Social network comments and posts model in PostgreSQL 9.6

Related Question