Cphalcon: Insert many rows at one time

[mrspartak]

I didn't find the way to do that. All methods, include phql, allows to add only one row at one time.

[niden]

@mrspartak

There is no way to add rows in bulk using PHQL. You will need to use raw SQL for that - provided that your RDBMS supports it

 $this->db->query("INSERT INTO .... (1,2,3), (4,5,6)....");

Alternatively you can create an array with elements each being a SQL statement and then loop through it to execute them. This way you will know what has been inserted and what not and you can also enclose everything in a transaction.

[mrspartak]

And will not be way to do this with PHQL or standart model?
Sometimes I need to insert a lot of rows (>100 000) and one by one way is too slow.

[Agent-J]

+1

[niden]

@mrspartak No there is no way to do this with PHQL unless you are issuing one statement at a time (i.e. one insert). PHQL is designed that way i.e. not to allow more than one statement at a time, to avoid SQL injections.

I think your best bet is to use the query method to just dump the combined sql statement in your db.

Alternatively you can execute a shell command using the import feature of mysql like so:

$user = "me";
$password = "mysecret";
$host = "localhost";
$dbname = "customers";
$path_to_file = "/var/dump";
$output = array();

exec("mysql -u{$user} -p{$password} -h{$host} {$dbname} < {$path_to_file}/file.sql", $output);

var_dump($output);

The above is faster for sure but you have to know what you are doing and your file has to be secure, correct etc.

[mrspartak]

I see. That is horrible, because it is standart behavior to insert many rows. I need to insert rows that I had calculated just now and can't do it throw snapshot.
Now I have to choose: make my own class that will check data and use $this->db->query method, or use another ORM.

[niden]

@mrspartak It will depend on how many times you actually need to insert the data and how critical is that operation to your application.

From my experience: If this is an operation that happens once a month, once a week or even once a day, creating a small piece of code that could effectively be run from the CLI would do the work and would by no means impede your project. If this however is a common task, that can be performed by any user, you can still use the db->query method by wrapping it in a class. If that is the case, providing some progress meters will give a visual representation of what is going on and even if it is slow it will be far better than a white screen or a loading cursor.

As for it being standard behavior I agree - we all need to import data at some point. Having seen though other frameworks, pretty much all of them use the same methodology. Some might even allow you to insert more than one at a time but as far as Phalcon is concerned we do not want to do that since it will increase the chances of SQL Injection attacks. Using raw SQL will allow you to insert more than one record at a time (again provided that your RDBMS supports it).

[mrspartak]

Oh, I thought this issue was closed :)

I understand your position.

[niden]

@mrspartak Send me an email at [email protected] so that we can discuss the issue. Perhaps there is a simple solution after all :)

[mrthebob]

@niden: Doing this would not create multiple queries:

INSERT INTO Model (col1, col2) VALUES ('val1', 'val2'), ('val3', 'val4'), ...

If you were in multiple query mode, this query would be followed by a semicolon and then the next query. It's a single query. It would just insert multiple records, so that you don't have to make multiple calls to the database.