SitePoint Sponsor

User Tag List

Results 1 to 4 of 4
  1. #1
    SitePoint Evangelist
    Join Date
    Feb 2006
    Location
    Worcs. UK
    Posts
    404
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Rails - join creates wrong ID

    I'm creating a stock register. Part of the system has three tables:

    stocks = each item of stock (belongs_to product)
    products = all the products we sell (belongs_to product_type and has_many stocks)
    product_types = a grouping of products (has_many products)

    I want to list all Stock that is of a Product in a particular ProductType. To do this I am using the following:
    controller
    Code:
        @product_type = ProductType.find(params[:id])
        @stocks = Stock.find(:all,
                              :conditions => "pr.product_type_id = #{@product_type.id}",
                              :joins => "as st right join products as pr on st.product_id = pr.id")
    and in the view
    Code:
       <% for stock in @stocks %>
       <tr>
        <td><%= stock.name %></td>
    In a cell of the table I want to create a link to the details of that stock item, so users can drill in for more detail:

    Code:
        <td>
          <%= link_to "Detail", :controller => 'stocks', :action => 'show', :id => stock.id %>
        </td>
    However, the output is coming up with the product.id instead of stock.id. This means that the resulting link is incorrect.

    I've directly entered the equivalent SQL code into MySQL to see what comes out:
    Code:
    @stock_pagesSELECT *
    FROM stocks as st
    right join products as pr
    on st.product_id = pr.id
    where pr.product_type_id = 9
    (manually entering a product_type.id) and the resulting table has an id field containing the correct stock.id.

    I've also tried to use find_by_sql options and get the same result. The original version used pagination and again the pagination by_sql options produced the same result - correct data in the table, but the wrong id for stock.id

    Am I doing something wrong? Can anyone suggest a way that I can force the @stock collection to return the correct stock.id

    I wondered if the problem was caused by the only named reference to an id in the SQL is "pr.id" and therefore that is getting picked up somewhere in the Ruby code. However, creating a dummy entry with "st.id = st.id" didn't help.

  2. #2
    SitePoint Evangelist
    Join Date
    Feb 2006
    Location
    Worcs. UK
    Posts
    404
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I found a fix!

    http://www.ruby-forum.com/topic/66359#127214

    By adding a :select condition to the @stocks query, forced the system to return the correct id. The new code is:
    Code:
        @stocks = Stock.find(:all,
                 :select => "st.*",
                 :conditions => "pr.product_type_id = #{@product_type.id}",
                 :joins => "as st right join products as pr on st.product_id = pr.id")

  3. #3
    SitePoint Enthusiast
    Join Date
    Aug 2004
    Location
    FL
    Posts
    72
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Also, It may not be the solution to your problem, but, you may want to check out Associations. You can easily do @products.stocks.stock_types.id, which will automatically create table joins based on your belong_to's and has_many's. For more info refer to the "Associations" part of the Rails API:

    http://api.rubyonrails.com

    Good Luck!
    Eric A.
    Founder and CEO, XMG Networks, Inc.
    XMG Services: Web 2.0 Photo Management and Sharing | Web Hosting
    Personal Blog: From Two 2 Twelve

  4. #4
    SitePoint Evangelist
    Join Date
    Feb 2006
    Location
    Worcs. UK
    Posts
    404
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thank you for your comment Eric.

    I have to admit to having the feeling that there should be a better way of doing this. At the moment, I've got a solution that works, but this section of code is on my list to refactor.

    One of the things I love about Ruby, is that in my experience refactoring not only simplifies the code, but allows me to either use it more often, or easily add additional functionality. It is never simply a tidying exercise.

    I look forward to following up your suggestion of checking out associations, and then having a "if it works like that, I can also do this, this, and this" moment.


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •