CODEWITHSUNDEEP
sqlpostgresql
Sergey Kozlov
Sergey Kozlov

Reputation: 452

Postgesql: count contains tags for items the tag belongs to the specified user

I have a two table that contains varchar:

items_tags_sets: items, tags

and

users: user_id, name.

How to make a request that will show tags and the number of identical tags to my item. The request should also include in the result a pointer (its_tag) to the fact that the tag belongs to the specified user.

CREATE TABLE items_tags_sets (
uit_set_id varchar PRIMARY KEY,
item_id varchar,
user_id varchar,
tag varchar
);

CREATE TABLE users (
user_id varchar PRIMARY KEY,
name varchar
);

insert into items_tags_sets values('uid1', 'it1', 'usr1', 'tag_test1');
insert into items_tags_sets values('uid2', 'it2', 'usr2', 'tag_test1');
insert into items_tags_sets values('uid3', 'it3', 'usr3', 'tag_test2');
insert into items_tags_sets values('uid4', 'it4', 'usr1', 'tag_test2');
insert into items_tags_sets values('uid5', 'it1', 'usr2', 'tag_test3');
insert into items_tags_sets values('uid6', 'it2', 'usr3', 'tag_test3');
insert into items_tags_sets values('uid7', 'it3', 'usr1', 'tag_test4');
insert into items_tags_sets values('uid8', 'it4', 'usr2', 'tag_test4');
insert into items_tags_sets values('uid9', 'it1', 'usr3', 'tag_test5');
insert into items_tags_sets values('uid10', 'it2', 'usr1', 'tag_test5');
insert into items_tags_sets values('uid11', 'it1', 'usr2', 'tag_test1');
insert into items_tags_sets values('uid12', 'it1', 'usr3', 'tag_test1');
insert into items_tags_sets values('uid13', 'it1', 'usr2', 'tag_test3');

insert into users values('usr1', 'name1');
insert into users values('usr2', 'name2');
insert into users values('usr3', 'name3');
--------------------------------------------------------------
SELECT
  items_tags_sets.item_id,
  items_tags_sets.tag,
  count(items_tags_sets.tag) as tags_count,
  items_tag_you.user_id as its_tag

FROM items_tags_sets
LEFT OUTER JOIN users as items_tag_you on items_tag_you.user_id = items_tags_sets.user_id and items_tag_you.user_id = 'usr1'
WHERE items_tags_sets.item_id = 'it1'
GROUP BY items_tags_sets.item_id, items_tags_sets.tag, items_tag_you.user_id;

I need a result like this:

----------------------------------------------
| item_id |    tag    | tags_count | its_tag |
|--------------------------------------------|
|   it1   | tag_test1 |      3     |  usr1   |
|   it1   | tag_test3 |      2     |         |
|   it1   | tag_test5 |      1     |         |
----------------------------------------------

Link to code: https://rextester.com/GMJQR57790

Upvotes: 0

Views: 39

Answers (1)

Belayer
Belayer

Reputation: 14921

Instead of function "count() ... group by" use the windowed function counter part "count() over ...". Order descending by the result of that count then use the "distinct on" clause also on the count.

select distinct on (3)
        its.item_id                                "Item Id"
      , its.tag                                    "Tag" 
      , count(its.tag) over (partition by its.tag) "Tags Count" 
      , usr.user_id                                "Its Tag"
   from items_tags_sets its
   left join users      usr 
     on (    usr.user_id = its.user_id 
          and usr.user_id = 'usr1'
        ) 
  where its.item_id = 'it1'
  order by 3 desc;

I couldn't figure out why you would alias a simple table name "users" with the more complex "items_tag_you" thus setting up a much more difficult to understand. So I didn't, but I did use a simple alias for both tables. Tip of the day: Make your queries as simple to read as possible, you can always add more descriptive terms on the final output.

Upvotes: 1

Related Questions