CODEWITHSUNDEEP
postgresql
Sergey Kozlov
Sergey Kozlov

Reputation: 452

Postgresql Number of matches in matches

I have three tables: t_items, market, items_likes.

create table t_items (
item_id varchar PRIMARY KEY,
title varchar);

create table market(
market_id varchar PRIMARY KEY,
item_id varchar references t_items(item_id),
title varchar);

CREATE TABLE items_likes (
like_id varchar PRIMARY KEY,
item_id varchar references t_items(item_id),
user_id varchar);

insert into t_items values('it1', 'item1');
insert into t_items values('it2', 'item2');
insert into t_items values('it3', 'item3');
insert into t_items values('it4', 'item4');
insert into t_items values('it5', 'item5');

insert into market values('mar1', 'it1', 'market1');
insert into market values('mar2', 'it2', 'market2');
insert into market values('mar3', 'it3', 'market3');
insert into market values('mar4', 'it4', 'market4');
insert into market values('mar5', 'it5', 'market5');
insert into market values('mar6', 'it1', 'market5');
insert into market values('mar7', 'it2', 'market5');
insert into market values('mar8', 'it1', 'market5');
insert into market values('mar9', 'it1', 'market5');
insert into market values('mar10', 'it3', 'market3');

insert into items_likes values('li1', 'it1', 'user1');
insert into items_likes values('li2', 'it1', 'user1');
insert into items_likes values('li3', 'it2', 'user2');
insert into items_likes values('li4', 'it2', 'user2');
insert into items_likes values('li5', 'it3', 'user3');
insert into items_likes values('li6', 'it3', 'user3');
insert into items_likes values('li7', 'it4', 'user4');
insert into items_likes values('li8', 'it4', 'user4');
insert into items_likes values('li9', 'it5', 'user5');
insert into items_likes values('li0', 'it5', 'user5');

I need a request that shows the name of the t_items, the names of the markets and the number of markets that have this t_items. Here is my request:

select
  t_items.item_id,
  t_items.title,
  market.market_id,
  market.title,
  count(distinct items_likes.like_id) as likes_count,  
  count(distinct market.market_id ) as market_count
from market

left join t_items on market.item_id = t_items.item_id
left join items_likes on t_items.item_id = items_likes.item_id
where t_items.title = 'item3'
group by t_items.item_id, market.market_id
order by t_items.title;

This query returns data without counting the number of markets. Finally, I want to get a table like this:

  | item_id | title | market_id | title   | likes_count | market_count
1 | it3     | item3 | mar10     | market3 |      2      |     2               
2 | it3     | item3 | mar3      | market3 |      2      |     2

Here is a working example https://rextester.com/EAA9855.

Use of the from market is mandatory.

Thank.

Upvotes: 0

Views: 54

Answers (2)

Zeus-Adenilton
Zeus-Adenilton

Reputation: 813

Combining "distinct on" and "with" we have:

WITH foo AS
  (SELECT t_items.item_id,
          t_items.title,
          market.market_id,
          market.title,
          items_likes.like_id
   FROM market
   LEFT JOIN t_items ON market.item_id = t_items.item_id
   LEFT JOIN items_likes ON t_items.item_id = items_likes.item_id
   WHERE t_items.title = 'item3'
   ORDER BY t_items.title)
SELECT DISTINCT ON (foo.item_id,
                    foo.market_id) *,

  (SELECT count(DISTINCT foo.like_id) FROM foo) likes_count,    
  (SELECT count(DISTINCT foo.market_id) FROM foo) market_count
FROM foo;

In this case, distinct on is working like group by and "with" will leave the query in cache not to affect performance in the subquery

Upvotes: 1

Humpelstielzchen
Humpelstielzchen

Reputation: 6441

I hope this works and it is what you need. Did this rather hasty.

select  t_items.item_id,
        t_items.title,
        c.market_id,
        c.title,
        c.market_count,
        count(distinct items_likes.like_id) as likes_count 

from (select item_id, market_id, title, (select count(distinct market_id) as market_count
                                         from market a
                                         where a.item_id = b.item_id)
      from market b
      group by item_id, market_id, title) c

left join t_items on c.item_id = t_items.item_id
left join items_likes on t_items.item_id = items_likes.item_id
where t_items.title = 'item2'
group by t_items.item_id, t_items.title, c.market_id, c.title, c.market_count;


 item_id | title | market_id |  title  | market_count | likes_count
---------+-------+-----------+---------+--------------+-------
 it3     | item3 | mar10     | market3 |            2 |     2
 it3     | item3 | mar3      | market3 |            2 |     2
(2 Zeilen)

for item 2

 item_id | title | market_id |  title  | market_count | likes_count
---------+-------+-----------+---------+--------------+-------
 it2     | item2 | mar2      | market2 |            2 |     2
 it2     | item2 | mar7      | market5 |            2 |     2
(2 Zeilen)

```

Upvotes: 1

Related Questions