CODEWITHSUNDEEP
rubypostgresqlsequel
user1903663
user1903663

Reputation: 1725

SEQUEL Postgres join query

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!

Upvotes: 1

Views: 419

Answers (2)

user1903663
user1903663

Reputation: 1725

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.

Upvotes: 1

Jeremy Evans
Jeremy Evans

Reputation: 12139

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.

Upvotes: 0

Related Questions