jopa.sql

with coins_cte as (select c.request_id,
                          jsonb_agg(
                                  jsonb_build_object(
                                          'url', url,
                                          'address', address,
                                          'symbol', symbol,
                                          'amount', amount
                                  )
                          ) as coins
                   from hamster_schema.request_coins c
                   group by c.request_id),
     nfts_cte as (select n.request_id,
                         jsonb_agg(
                                 jsonb_build_object(
                                         'url', url,
                                         'address', address,
                                         'name', name,
                                         'description', description
                                 )
                         ) as nfts
                  from hamster_schema.request_nfts n
                  group by n.request_id),
     bids_cte as (select b.deal_id,
                         jsonb_agg(
                                 jsonb_build_object(
                                         'id', b.id,
                                         'error', b.error,
                                         'user_address', b.user_address,
                                         'items', jsonb_build_object(
                                                 'coins', coalesce(c.coins, '[]'::jsonb),
                                                 'nfts', coalesce(n.nfts, '[]'::jsonb)
                                         )
                                 )
                         ) as responses
                  from hamster_schema.bids b
                           left outer join coins_cte c on c.request_id = b.request_id
                           left outer join nfts_cte n on n.request_id = b.request_id
                  group by b.deal_id),
     participated_bids_cte as (
         select b.deal_id,
                jsonb_agg(
                                 jsonb_build_object(
                                         'id', b.id,
                                         'error', b.error,
                                         'user_address', b.user_address,
                                         'items', jsonb_build_object(
                                                 'coins', coalesce(c.coins, '[]'::jsonb),
                                                 'nfts', coalesce(n.nfts, '[]'::jsonb)
                                         )
                                 )
                         ) as responses
                  from hamster_schema.bids b
                           left outer join coins_cte c on c.request_id = b.request_id
                           left outer join nfts_cte n on n.request_id = b.request_id
                  where b.user_address = '123'
                  group by b.deal_id
     ),
     deal_requests_cte as (select d.request_id,
                           jsonb_agg(
                                 jsonb_build_object(
                                         'id', d.id,
                                         'error', d.error,
                                         'user_address', d.user_address,
                                         'items', jsonb_build_object(
                                                 'coins', coalesce(c.coins, '[]'::jsonb),
                                                 'nfts', coalesce(n.nfts, '[]'::jsonb)
                                         )
                                 )
                         ) as request
                           from hamster_schema.deals d
                                    left outer join coins_cte c on c.request_id = d.request_id
                                    left outer join nfts_cte n on n.request_id = d.request_id
                           group by d.id)
select d.*,
       dr.request_id,
       dr.request,
       coalesce(pb.responses, '[]'::jsonb) as responses
from hamster_schema.deals d
         join deal_requests_cte dr on dr.request_id = d.request_id
         join participated_bids_cte pb on pb.deal_id = d.id

union all
select d.*,
       dr.request_id,
       dr.request,
       coalesce(b.responses, '[]'::jsonb) as responses
       from hamster_schema.deals d
join deal_requests_cte dr on dr.request_id = d.request_id
    join bids_cte b on b.deal_id = d.id

where d.user_address = '123'