ActiveRecord :: How To create a Record with a specific ID


#1

Hi folx,

may somebody could help me here?

Problem:

ID cannot be set, while creating or updating a record.
We recieve vacancies in a csv format, which we import. But the ID
changes every time because it increments at each import and it should
actually be the customer_id.

Vacancy.create(:id => 10200, :first_name => “John”, :last_name =>
“Doe”)

doesn’t work

Does anybody know, how I could get inserted the records with their
foreign ID from the csv export?

Thanks a lot in advance for your help

Rafael


#2

Add customer_id to Vacancy and do Vacancy.create(:customer_id =>
10200, :first_name => “John”, :last_name => “Doe”). This raises the
question where Customer with corresponding id will come from, but it’s
up to you how to solve this problem. Don’t mess with primary key,
anyways.


#3

Thats what I have already, but what I don’t want.
On every import cycle, the show id changes for the same vacancy.

so that knowbody can send a link to anybody, because after the import,
the id wont fit any more.

somebody else has a clue?


#4

On Jun 14, 2007, at 8:10 AM, liquidautumn wrote:

Problem:

Does anybody know, how I could get inserted the records with their
foreign ID from the csv export?

Thanks a lot in advance for your help

Rafael

I think you should seriously consider what liquidautumn says about
the primary key (do you actually have a Customer model?), but what
you want can be done.

Vacancy.new(:first_name => “John”, :last_name => “Doe”) do |v|
v.id = 10200
v.save
end

Now that you have this power, please use it wisely.

-Rob

Rob B. http://agileconsultingllc.com
removed_email_address@domain.invalid


#5

You can just skip the helpers and interact more closely with the DB.
You’ll want to do this for performance reasons as well, if you are
importing large numbers of records.

Vacancy.connection.insert(‘insert into vacancies (id, first_name,
last_name) values (10200, “John”, “Doe”)’)


Benjamin C.
http://www.bencurtis.com/ – blog
http://agilewebdevelopment.com/rails-ecommerce – build e-commerce
sites with Rails


#6

I don’t know if this is really the “best practice” way of doing this,
but this should work:

vacancy = Vacancy.new(:first_name => ‘John’, :etc => ‘blah’) do |v|
v.id = 10200
end
vacancy.save!

You will presumably get an exception if you try and save something
with the same id, so you will need to check for it first (and probably
wrap the whole thing up in a transaction unless you know you’re the
only one doing inserts).

Cheers,
Roland


#7

If the value changes it is not the primary key. Just let the system
create the primary key for you.

If you need to be able to replace records with new ones just delete
the old ones before the import. If you are updating entries then find
the correct record based on the stable data and then update from the
imported row.

Michael


#8

On Jun 14, 2007, at 8:35 AM, Rob B. wrote:

may somebody could help me here?
“Doe”)
I think you should seriously consider what liquidautumn says about
-Rob

Rob B. http://agileconsultingllc.com
removed_email_address@domain.invalid

On Jun 14, 2007, at 8:18 AM, Rafael wrote:

Thats what I have already, but what I don’t want.
On every import cycle, the show id changes for the same vacancy.

so that knowbody can send a link to anybody, because after the import,
the id wont fit any more.

somebody else has a clue?

On Jun 14, 2007, at 8:53 AM, Benjamin C. wrote:

http://agilewebdevelopment.com/rails-ecommerce – build e-commerce
sites with Rails

Ah, perhaps you need something like:

vacancy =
Vacancy.find_or_initialize_by_id_and_first_name_and_last_name(10200,
“John”, “Doe”)
if vacancy.new_record?
vacancy.id = 10200
vacancy.save
end

But you might get better performance using Benjamin’s suggestion if
you just need the “insert only if it’s not there” behavior.

However, if this CSV file can change the name on a specific vacancy:

if vacancy = Vacancy.find_by_id(10200)
vacancy.update_attributes(:first_name => “John”, :last_name => “Doe”)
else
vacancy = Vacancy.new(:first_name => “John”, :last_name => “Doe”)
do |v|
v.id = 10200
v.save
end
end

-Rob

Rob B. http://agileconsultingllc.com
removed_email_address@domain.invalid


#9

Hi everybody,

first thank you all for your help!
This helps a lot. But actually not in this project anymore :frowning:

The client changed their Export and their unique key is now something
like this 130003-31.
because it isn’t an integer anymore I had to find another solution: I
go for is a REST Solution now.

vacancies/130003-31

this show’s me always the right ID, because I changed the select to
REST URL vacancy_path(vacancy.vacancy_number)
show action
find_by_vacancy_number(params[:id])

This takes me to another question, but this I will try to post in
another topic.

Thanks anyway! I tried the “connection” solution, and it is the best
solution except, that their is a quoting problem with special chars.

Greetinx

Rafael