Rails group also returns count of duplicate rows

36
February 12, 2019, at 12:40 PM

TLDR -
I am trying to do group on this returned collection of distinct rows. But for some reason, it also counts rows with duplicate procurement_id. Why is that?

My result from the sql query, note that rows returned with status_tag_id = 8 is only 5

[#<Tags:0x00005650a88bd3b0 id: nil, procurement_id: 2, status_tag_id: 7>,
     #<Tags:0x00005650a88bcc08 id: nil, procurement_id: 3, status_tag_id: 7>,
     #<Tags:0x00005650a88bc370 id: nil, procurement_id: 4, status_tag_id: 7>,
     #<Tags:0x00005650a88cb988 id: nil, procurement_id: 5, status_tag_id: 7>,
     #<Tags:0x00005650a88cb4b0 id: nil, procurement_id: 7, status_tag_id: 7>,
     #<Tags:0x00005650a88ca790 id: nil, procurement_id: 15, status_tag_id: 7>,
     #<Tags:0x00005650a88c9d68 id: nil, procurement_id: 273, status_tag_id: 7>,
     #<Tags:0x00005650a88c9840 id: nil, procurement_id: 326, status_tag_id: 7>,
     #<Tags:0x00005650a88c91b0 id: nil, procurement_id: 397, status_tag_id: 7>,
     #<Tags:0x00005650a88c86e8 id: nil, procurement_id: 590, status_tag_id: 7>,
     #<Tags:0x00005650a88c8030 id: nil, procurement_id: 645, status_tag_id: 7>,
     #<Tags:0x00005650a88cfc18 id: nil, procurement_id: 650, status_tag_id: 7>,
     #<Tags:0x00005650a88cf8f8 id: nil, procurement_id: 1707, status_tag_id: 7>,
     #<Tags:0x00005650a88cf538 id: nil, procurement_id: 2282, status_tag_id: 7>,
     #<Tags:0x00005650a88ceef8 id: nil, procurement_id: 127, status_tag_id: 8>,
     #<Tags:0x00005650a88cea48 id: nil, procurement_id: 8, status_tag_id: 8>,
     #<Tags:0x00005650a88ce278 id: nil, procurement_id: 601, status_tag_id: 8>,
     #<Tags:0x00005650a88cdb20 id: nil, procurement_id: 782, status_tag_id: 8>,
     #<Tags:0x00005650a88cd288 id: nil, procurement_id: 2270, status_tag_id: 8>]

NOTE with procurement_id: 650, there are 4 duplicate rows with the same id but different status_tag_id, so i do DISTINCT on procurement_id. Similarly with few other rows.

AR query

Tag.distinct.select('procurement_id, status_tag_id')
    .with_procurement_ids(pluck(:id))
    .with_status_tag_ids(["7","8"])
Tag Load (9.1ms)  
SELECT DISTINCT procurement_id, status_tag_id
 FROM `tags` 
WHERE `tags`.`procurement_id` IN (18, 17, 16, 15, 13, 10, 8, 7, 6, 5, 4, 3, 2)
  AND `tags`.`status_tag_id` IN (7, 8)`

Now when I try to group, it also counts duplicate rows.

AR query

Tag.distinct.select('procurement_id, status_tag_id')
    .with_procurement_ids(pluck(:id))
    .with_status_tag_ids(["7","8"])
    .group(:status_tag_id).size

SELECT COUNT(DISTINCT `tags`.`id`) AS count_id
     , tags.status_tag_id tags_status_tag_id 
  FROM tags
 WHERE tags.procurement_id IN (18, 17, 16, 15, 13, 10, 8, 7, 6, 5, 4, 3, 2)  
   AND tags.status_tag_id IN (7, 8) 
 GROUP 
    BY tags.status_tag_id

with the output

{7=>22, 8=>7}

What i am expecting is

{7=>14, 8=>5}

What is wrong?

Why is group also counting duplicate rows? Why is group not working just on the returned collection?

READ ALSO
Query to update particular column field in SQL

Query to update particular column field in SQL

I tried the following queries to change the column field of the table 'role' from 'Admin' to 'Administrator'I am not informed about the values of id

46
How to query a date using strftime to format

How to query a date using strftime to format

I am trying to grab all the records for the monthThe string I have to query with is in this format 2019-01-12

39
Extract data from a database if a specific value in is followed by a another specific value

Extract data from a database if a specific value in is followed by a another specific value

I have a database in MySQL where one of the columns consists of different types of events and I want to extract specific data to pythonFor example:

56
SQL, Group by one column with one distinct column

SQL, Group by one column with one distinct column

I have a table with following structure

42