Query the database using a non-id column

Question

I have what I think is a non standard model strcture (i know that it is bad, but its neccessary). Given the following migration :

create_table :MODELS, :primary_key => :MOD_ID do |t|
  t.integer :MOD_ID
  t.integer :MOD_MFA_ID
  t.integer :MOD_CDS_ID
  t.integer :MOD_PCON_START
  t.integer :MOD_PCON_END
  t.integer :MOD_PC
  t.integer :MOD_CV
  t.integer :MOD_AXL
  t.binary :MOD_PC_CTM
  t.binary :MOD_CV_CTM

  t.timestamps

The MOD_MFA_ID corresponds to an association to another table/model (manufacturers). I would like to retrieve models based on the manufacturer's brand column.

  def getmanufacturer 
    @manufacturer = Manufacturers.find(params[:manufacturer])

    respond_to do |format|
      format.html # show.html.erb
      format.xml  { render :xml => @manufacturer }
    end
  end

I can't figure out how to modify my controller and routing to handle this use case. I'd like to query the app using this kind or URL : /models?manufacturer=audi&&model=a6

Ben Miller · Accepted Answer · 2012-04-15 18:45:23Z

3

So saying you want a URL like:

/manufacture/audi/model/a6

in your routes file do:

resources manufactures do
  resources models
end

For the models you would want to switch your primary key

Manufacturer model:

set_primary_key :manufacturer_name #what ever you want used instead of ID

Do the same thing for your MODEL model

set_primary_key :MOD_Name

Then in your controller you can do:

Manufacturer.find(params[:manufaturer_id]).models.find(params[:id])

If you don't want to switch the primary keys, you can also do find_by:

Manufacturer.find_by_manufacturer_name("Audi").models.find_by_model_name("a6")

answered Apr 15, 2012 at 18:45

Ben Miller

1,48412 silver badges14 bronze badges

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

6 Comments

byCoder Over a year ago

that's good, but on first step I in model set_primary_key "MFA_ID", but in controller i wanna find not by MFA_ID, but for MOD_Name ??? edit: sorry) i'm realy stupid)) it's no good to learn for 18/7

Ben Miller Over a year ago

You can use the find_by methods. So try .find_by_mod_name("a6")

byCoder Over a year ago

I meen url like /audi/a6/ if it could be?

byCoder Over a year ago

also, in finb_by give array or one entry?

Ben Miller Over a year ago

you can pass the same options to find_by that you pass to find

|

Marlin Pierce · Accepted Answer · 2012-04-15 18:55:22Z

0

If you have a migration and are creating a new database, then you should make a standard db. I understand if you are connecting to a database that you were not able to design. I'd also use lowercase for the field names.

If you want MFA_BRAND to be the primary key, then you can set the primary key in your class. Suppose you want MFA_ID to be the primary key, then you would add this to your class as:

class Model < ActiveRecord::Base
  set_primary_key "MFA_ID"
end

This will change the find and to_param methods to use your primary key field.

However, assuming you don't want to change the primary key, but sometimes want to search by MFA_BRAND, then you will want to pass the MFA_BRAND value as a CGI parameter. First set teh routes as

resources :models do
  collection do
    get :getmanufacturer
  end
end

When you need the path helper call it this way:

getmanufacturer_models_path(:manufacturer => "Acme")

the URL will be

/models/getmanufacturer?manufacturer=Acme

answered Apr 15, 2012 at 18:55

Marlin Pierce

10.1k5 gold badges33 silver badges56 bronze badges

2 Comments

Ben Miller Over a year ago

He already stated it was necessary to have a non standard DB. Often times a DB is shared among multiple applications. The question was how to deal with the limitation.

Marlin Pierce Over a year ago

I did give him a solution to deal with the non-standard db. I saw that he was using a migration, which makes me think he has some latitude to control his database and translate. However, if he truely doesn't, I still gave him a solution to deal with it.

Collectives™ on Stack Overflow

Query the database using a non-id column

2 Answers 2

6 Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

6 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related