CODEWITHSUNDEEP
sqlruby-on-railspostgresqlactiverecord
Gearnode
Gearnode

Reputation: 693

SQL JOIN query create duplication with PostgreSQL

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

Upvotes: 1

Views: 62

Answers (2)

Gearnode
Gearnode

Reputation: 693

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

Upvotes: 0

Hoang Phan
Hoang Phan

Reputation: 2166

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.

Upvotes: 1

Related Questions