CODEWITHSUNDEEP
javasqlspringspring-bootjpa
user13505963
user13505963

Reputation:

Inner join union not same amounth of columns

    @Query(value="
    select * from (select * from Post WHERE Post.id < :lastPostId) as p 
    JOIN Follower f ON(f.to_id = p.user_id) WHERE f.from_id = :#{#currentUser.id} 
    UNION 
    select * from (select * from Post WHERE Post.id < :lastPostId) as p 
    JOIN public.user u ON(u.group_id = :#{#currentUser.group.id})"
    List<Post> getNewsFeedPosts(User currentUser,Long lastPostId);

Hello im trying to union two queries, but im getting exception so union doesnt have same amounth of columns.

Can anybody help, maybe how to improve query

Upvotes: 1

Views: 55

Answers (1)

user13505963
user13505963

Reputation:

Based on previous answers i came up with this solution

   @Query(nativeQuery = true, value =
            "WITH posts as (SELECT * FROM post p WHERE p.id < :lastPostId) " +
            "select p.id,p.user_id,p.created_at,p.description,p.name FROM posts p JOIN Follower f ON(f.to_id = p.user_id) WHERE f.from_id = :#{#currentUser.id} " +
            "UNION " +
            "SELECT p.id,p.user_id,p.created_at,p.description,p.name FROM posts p JOIN public.user u ON(u.group_id = :#{#currentUser.group.id} ) ORDER BY id DESC LIMIT 5")
List<Post> getNewsFeedPosts(User currentUser,Long lastPostId);

Upvotes: 1

Related Questions