SEQUEL Postgres join query

Question

I am struggling with a simple join query using postgres and SEQUEL with ruby.

I have table 1 (shortlists) with columns: id, shortname

and table 2 (shortmembers) with columns: id, short_id (references to the id of shortlists)

I want to select shortmembers id and shortlists shortname. So, in regular SQl, it would be

SELECT shortlists.shortname, shortmembers.id WHERE shortmembers.short_id = shortlists.id

I have tried

@shortmembers = DB[:shortlists].join(:shortmembers, :id => :short_id)

and then ruby code:

<option value="">Populated Shortlists ..</option>
        <% @shortmembers.each do |shortmember| %>
        <option value="<%= shortmember[:id] %>"><%= shortmember[:shortname] %></option>
        <% end %>

I get an error message which leads me to suppose the join query is not working. It is:

undefined method `each' for nil:NilClass

All help gratefully received!

user1903663 · Accepted Answer · 2013-05-29 00:23:20Z

1

I have figured it out. The correct query is:

@shortmembers = DB[:shortlists].distinct(:shortname).join(:shortmembers, :short_id => :id)

As an observation, in passing, distinct is important otherwise each example of the "shortname" will be repeated.

The problem was not the construction of the query but that the "id" column in the shortlists table was type integer and the corresponding short_id column, in the shortmembers table, was varchar type. Postgres got upset about that. When I changed the short_id to type "int" no problems.

I hope this helps somebody else.

answered May 29, 2013 at 0:23

user1903663

1,7453 gold badges23 silver badges46 bronze badges

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

Comments

Jeremy Evans · Accepted Answer · 2013-05-28 16:50:34Z

0

From the error message it appears that @shortmembers is nil in your template. From the earlier code you posted, it should be a dataset. So it looks like the template is not being executed in the same context/binding as the earlier code.

answered May 28, 2013 at 16:50

Jeremy Evans

12.2k32 silver badges28 bronze badges

1 Comment

user1903663 Over a year ago

Thanks I realize that. I am just wondering if the join query is correct. Do you think it is?

Collectives™ on Stack Overflow

SEQUEL Postgres join query

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related