What is difference between selecting users.* and selecting each individual columns?

Question

I'm working on studying Ruby on Rails, and have some specific questions about rails active records and their SQL conversions.

FYI, I'm using postgresql, and user model has many statuses, and I wanted to order users based on created_at column of statuses. Although I found out the solution, User.includes(:statuses).order('statuses.created_at desc'), I still have some (maybe inter-related) things I don't understand well.

1) On rails console, (I simplified for better readibility)

User.joins(:statuses).to_sql produces "SELECT users.* FROM users INNER JOIN statuses ON statuses.user_id = users.id".

User.includes(:statuses).references(:statuses).to_sql produces "SELECT users.id AS t0_r0, ...(simplified)... statuses.created_at AS t1_r3 FROM users LEFT OUTER JOIN statuses ON statuses.user_id = users.id"

What is difference between selecting users.* and selecting each individual columns?

2) Also on rails console,

User.joins(:statuses).size produces SELECT COUNT(*) FROM users INNER JOIN statuses ON statuses.user_id = users.id => 155.

User.includes(:statuses).references(:statuses).size produces SELECT COUNT(DISTINCT users.id) FROM users LEFT OUTER JOIN statuses ON statuses.user_id = users.id => 16.

Why includes automatically contains distinct clause while joins does not?

3) I tried to obtain distinct users ordered by statuses.created_at, with statuses joined on users.

I used this clause: User.joins(:statuses).select('users.*, statuses.created_at').order('statuses.created_at desc').distinct. (I should use select statuses.created_at due to PG::InvalidColumnReference: ERROR: for SELECT DISTINCT, ORDER BY expressions must appear in select list)

But this clause doesn't remove duplication! Although User.joins(:statuses).select!('users.*, statuses.created_at').order('statuses.created_at desc').distinct.size produces 16, when I actually execute it, I see a lot of duplications.

It produces SQL statement: SELECT DISTINCT users.*, statuses.created_at FROM users INNER JOIN statuses ON statuses.user_id = users.id ORDER BY statuses.created_at desc, and it shows the following image.

Result of User.joins(:statuses).select!('users.*, statuses.created_at').order('statuses.created_at desc').distinct

As you see, it shows duplications of my records.

So my third question is, why distinct clause doesn't remove duplications (and why size shows distinct result)?

Thank you in advance!

Answer 1

The joins method just generates a SQL join for you. Any individual user may be joined to multiple statuses - that's just what joins do, if this isn't what you need then it's up to you to deal with it. The select clause defaults to users.* so that you don't unwittingly end up with identically named columns across both tables shadowing each other (eg the id column)

includes on the other hand is for eager loading an association. That this is sometimes accomplished with a join is just an inplementation detail - there is extra code in the handling of the result so that the result set features each user exactly once (and with the status data fed into the association). Includes also aliases all the column names to handle identically named columns

Lastly your distinct clause isn't removing duplicates because the rows aren't identical - the statuses.created_at column is included, which will be different across most rows.

The size method ignores your select clause and so counts against users.id - in that case distinct will only count each user once