Joined search returns NIL ids


#1

I have two models: assets and makes. Make has many assets and an asset
belongs to one make.

I am calling this part of my asset controller from a search form with
the parameters “search” (search term) and “field” (database field
heading) which then renders a partial with the search results on it.

########################################################

if @params[‘field’] == ‘make_id’
@asset_pages, @assets = paginate :assets,
:conditions => [ “makes.name LIKE ?”,
‘%’ + @params[‘search’] + ‘%’ ],
:join => “JOIN makes on assets.make_id =
makes.id”,
:per_page => 15
@mark_term = @params[‘search’]
render :partial => ‘results’
end

########################################

I am trying to search for assets having a make with a certain name (name
is a field in the makes table). The code works and brings back results,
however the ID of every asset is nil, which is then rendered as 4 (why
is this?). This means that all the show/edit/destroy links in my list
view don’t work. Every other attribute of the assets is retrieved
correctly. What am I doing wrong?

Many thanks,

Chris B.


#2

Take a look at the raw SQL in your log file. My guess is that the
“JOIN” in the :join param is not needed and may be causing problems.

On Monday, April 24, 2006, at 1:22 PM, Chris B. wrote:

########################################################
end

http://lists.rubyonrails.org/mailman/listinfo/rails
_Kevin


#3

Kevin O. wrote:

Take a look at the raw SQL in your log file. My guess is that the
“JOIN” in the :join param is not needed and may be causing problems.

On Monday, April 24, 2006, at 1:22 PM, Chris B. wrote:

########################################################
end

http://lists.rubyonrails.org/mailman/listinfo/rails
_Kevin

But then mysql does not know that makes and assets are linked to each
other:

Mysql::Error: Unknown table ‘makes’ in where clause: SELECT count(*) AS
count_all FROM assets WHERE (makes.name LIKE ‘%tha%’)

There must be a way to do this relationship easily in ruby?

Chris


#4

Chris B. wrote:

########################################

I am trying to search for assets having a make with a certain name (name
is a field in the makes table). The code works and brings back results,
however the ID of every asset is nil, which is then rendered as 4 (why
is this?). This means that all the show/edit/destroy links in my list
view don’t work. Every other attribute of the assets is retrieved
correctly. What am I doing wrong?

I think there are two issues here:

  1. Unless you add a
    :select => ‘assets.*’
    option to your paginate call you will get the id of the joined makes
    object rather than that of the asset.

  2. You are getting 4 because you are calling nil.id, and 4 is the
    internal
    Ruby id of the nil object. Check your code to see why the object on
    which you are calling the id method is nil.


We develop, watch us RoR, in numbers too big to ignore.


#5

Mark Reginald J. wrote:
are two issues here:

  1. Unless you add a
    :select => ‘assets.*’
    option to your paginate call you will get the id of the joined makes
    object rather than that of the asset.

I added that and it now works perfectly. Thank you very much!

Chris