How to avoid duplicate field name in query result

Question

I am developing a padrino web application. I have two tables:

User table: Has id, name, address fields
Post table: Has id, user_id, content

If I join them,

User.join(:posts, user_id:, :id)

they return two fields with the same name id:

id name address id user_id content
 1 jim   ***     3    1      post1

I would like to rename each of these ids. I want the result to be:

u_id name address p_id user_id content
1   jim  ***     3       1     post1

I am using Sequel adapter for postgresql db in padrino project. In mysql query, it will be like this:

select u.id as u_id, 
       u.name, 
       u.address, 
       p.id as p_id, 
       p.user_id as user_id, 
       p.content 
from users u 
  join posts p on u.id = p.user_id

what should I do? What I want is not sql query, but code of ruby language. Thanks.

That query will work in Postgres just as well. Note that assigning an alias that is the same as the column name is pretty much useless, so `p.user_id as user_id` is exactly the same as `p.user_id`. — , Nov 20 '17 at 09:07

score 1 · Accepted Answer · answered Nov 20 '17 at 15:17

You should use Dataset#select to set which columns are being selected:

User.from{users.as(:u)}.join(Sequel[:posts].as(:p), user_id: :id).
  select{[u[:id].as(:u_id), u[:name], u[:address],
          p[:id].as(:p_id), p[:user_id].as(:user_id), p[:content]]}

How to avoid duplicate field name in query result

1 Answers1