Welcome Guest, Not a member yet? Register   Sign In
Active Record: Insert / update on exist
#1

[eluser]Samus[/eluser]
Basically i'm inserting thousands of rows of data at once from a .txt file.

Problem is sometimes data may already exist, but may have changed ever since the last insert, so i'm going to need to update that data with the newer values.

I need help in creating an insert/update batch method.

I'm aware this functionality isn't currently available in 2.1, but i've seen posts on where people have been able to hack/extend the core to achieve this.

Anyone able to guide me to an up to date example of how to do this please?
#2

[eluser]Stefan Hueg[/eluser]
There are two approaches:

First:
http://dev.mysql.com/doc/refman/5.0/en/i...icate.html

This is what you need. You'll have to generate an SQL statement for every update on your own

Second:
1. Build up a flat array of IDs you wish to update (lets say: $my_ids)
2. Query your database for existing keys
Code:
$result = $this->db->select('id')->from('table')->where_in($my_ids)->get();
3. Use these results to update your DB and unset the keys of your $my_ids
4. Use the remaining keys for insertion
#3

[eluser]Samus[/eluser]
[quote author="Stefan Hueg" date="1336432856"]There are two approaches:

First:
http://dev.mysql.com/doc/refman/5.0/en/i...icate.html

This is what you need. You'll have to generate an SQL statement for every update on your own

Second:
1. Build up a flat array of IDs you wish to update (lets say: $my_ids)
2. Query your database for existing keys
Code:
$result = $this->db->select('id')->from('table')->where_in($my_ids)->get();
3. Use these results to update your DB and unset the keys of your $my_ids
4. Use the remaining keys for insertion[/quote]
Yeah that's actually similiar to what I was doing before.

It just isn't effecient at all checking then inserting/updating 30,000+ rows. PHP always times out.

I was thinking if there was a solution using something similiar to insert_batch() maybe with the 'insert on duplicate' clause
#4

[eluser]Stefan Hueg[/eluser]
You may have a look here: http://dev.mysql.com/doc/refman/5.1/de/i...layed.html

But honestly: 30.000 rows isn't that much and should never produce a timeout.

Are you sure that you set proper keys and indexes on the rows you are querying?
#5

[eluser]Samus[/eluser]
Well I wasn't too sure if the indexes affected inserting that much, but yeah I have a primary key on my ID column.
#6

[eluser]Stefan Hueg[/eluser]
Look at DB_active_rec.php in your system/database folder.
-public function insert_batch($table = '', $set = NULL)

and system/database/mysqli_driver:
function _insert_batch($table, $keys, $values)

The way how insert_batch works is that it will stitch one single SQL statement together and fireing it onto the database.

You should do the same but use the insert ... on duplicate... statement instead.

Either you'll fire 30.000 queries which is, admittedly, slow Big Grin
#7

[eluser]Samus[/eluser]
[quote author="Stefan Hueg" date="1336435111"]Look at DB_active_rec.php in your system/database folder.
-public function insert_batch($table = '', $set = NULL)

and system/database/mysqli_driver:
function _insert_batch($table, $keys, $values)

The way how insert_batch works is that it will stitch one single SQL statement together and fireing it onto the database.

You should do the same but use the insert ... on duplicate... statement instead.

Either you'll fire 30.000 queries which is, admittedly, slow Big Grin[/quote]
Indeed you're right there, that was my initial thought, but it involved 'hacking' the core and since all my CI apps share the same core, I could update someday and completely forget about that 'hack', which will probably end in me debugging it for the next 3 days.

Anyway thanks, if I don't get any more ideas, i'll just go ahead and edit that.
#8

[eluser]Stefan Hueg[/eluser]
Don't edit, EXTEND Smile That's what OOP is all about Big Grin
You could make a MY_Model (which I always do) and put your custom query functions there.

It's a common problem but I have not seen a better solution yet.
#9

[eluser]Samus[/eluser]
[quote author="Stefan Hueg" date="1336435660"]Don't edit, EXTEND Smile That's what OOP is all about Big Grin
You could make a MY_Model (which I always do) and put your custom query functions there.

It's a common problem but I have not seen a better solution yet.[/quote]
I guess i'll just have to copy the code from the system/db, edit and store in the model, huh
#10

[eluser]Stefan Hueg[/eluser]
I don't know if that works as I've never extended database classes, but here is a perfect solution. I just googled a bit:

http://dev.mysql.com/doc/refman/5.1/en/replace.html

With this you can just build your own replace_batch function and make us all happy Smile




Theme © iAndrew 2016 - Forum software by © MyBB