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.

Answer 1

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]]}