SQL JOIN query create duplication with PostgreSQL

Question

I have an issue with my SQL query, I try to join two tables, but as result have duplicated lines.

There is my database schema:

# messages
belongs_to :user
belongs_to :request

# requests
has_many :messages
belongs_to :expense

I have an polimorphic association between my expenses and requests tables.

And this my query:

Expense.
  joins('LEFT JOIN requests ON expenses.id = requests.integration_id').
  joins('LEFT JOIN messages ON requests.id = messages.request_id').
  where('expenses.id IN (?)', @expenses_id).
  select('DISTINCT messages.user_id AS user_id').
  group('messages.user_id, expenses.id')

I get all expenses duplicated when I try to joins messages table.

What happen?

EDIT:

With this query is same result:

Expense.
  joins('LEFT JOIN requests ON expenses.id = requests.integration_id').
  joins('LEFT JOIN messages ON requests.id = messages.request_id').
  where('expenses.id IN (?)', @expenses_id).
  select('DISTINCT messages.user_id AS user_id').
  group('messages.user_id')

EDIT 2:

When I try this:

Expense.
  joins('LEFT JOIN requests ON expenses.id = requests.integration_id').
  count(:id)
#=> 45

But when I join messages

Expense.
  joins('LEFT JOIN requests ON expenses.id = requests.integration_id').
  joins('LEFT JOIN messages ON requests.id = messages.request_id').
  count(:id)
#=> 93

Hoang Phan · Accepted Answer · 2016-01-06 05:04:30Z

1

OK I think I know the problem:

Rail's ActiveRecord will translate the field result into the model's attributes, and ignore anything outside. So let's say you have model A with attribute b, c, d. And your join result is:

 | b | c | d | e | f |
 | 1 | 2 | 3 | 1 | 2 |
 | 1 | 2 | 3 | 2 | 1 |
 | 1 | 2 | 3 | 2 | 2 |

Then you group by b and e

 | b | c | d | e | count(f) |
 | 1 | 2 | 3 | 1 | 1        |
 | 1 | 2 | 3 | 2 | 2        |

This will create 2 records since the query result returns 2 rows, but they are exactly the same when translate into model A (b = 1, c = 2, d = 3).

In your case, message.user_id is the field causing this.

Remove it from group clause will fix the problem, but I don't know for sure what do you want message.user_id to be used for.

edited Jan 6, 2016 at 5:04

answered Jan 6, 2016 at 5:01

Hoang Phan

2,1661 gold badge17 silver badges16 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Gearnode Over a year ago

I have try this query and is same result... (I have edit the post)

Hoang Phan Over a year ago

I think it should be group('expenses.id') instead

Gearnode Over a year ago

Yes, but i want group by user because my process need that for sum and count many expenses.

Hoang Phan Over a year ago

If you want to group by user, I suggest you do the joins from Message model instead of Expense model.

Gearnode Over a year ago

Same result with join from Message. But I have no duplicated result when i make count('DISTINCT expenses.id'). But when I sum I can't use DISTINCT.

Gearnode · Accepted Answer · 2016-01-07 00:41:34Z

0

For solve this issue, I needed adding specific select on my JOIN. Because, when I JOIN the messages table, I get many messages with same request_id.

SELECT messages.user_id, SUM(expenses.total_in_home_currency)
FROM expenses
INNER JOIN requests ON expenses.id = requests.integration_id
INNER JOIN
  (SELECT DISTINCT(messages.request_id), messages.user_id FROM messages) messages 
  ON requests.id = messages.request_id
WHERE expenses.id IN (76)
GROUP BY messages.user_id

answered Jan 7, 2016 at 0:41

Gearnode

6937 silver badges21 bronze badges

Collectives™ on Stack Overflow

SQL JOIN query create duplication with PostgreSQL

2 Answers 2

5 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

5 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related