sqlnode.jspostgresqlmassivejs

PostgreSQL UNION, but where do the results come from?

I want to do a union with two tables, but I need to know where the results are coming from. Here is my join.

(SELECT  * FROM watchlist_movies WHERE movie_id = $1 and user_id = $2)
UNION ALL
(SELECT* FROM favorite_movies WHERE movie_id = $1 and user_id = $2)

so my goal is return a single array with two objects in them. For example,

[
{watchList: [... results]},
{favorites: [... results]}
]

Is this result possible or would have to make two separate sql statements and pack them in a array in Node.js?

Solution

  • Try:

    (SELECT 'Watchlist', a.* FROM watchlist_movies a WHERE movie_id = $1 and user_id = $2)
UNION ALL
(SELECT 'Favorites', b.* FROM favorite_movies b WHERE movie_id = $1 and user_id = $2)