How to bulk insert with RedBeanPhp?

Question

I was hoping for an example on how to bulk insert new "beans" in readbeanphp without looping over each instance.

It shows an example creating and saving a beans here: http://redbeanphp.com/manual/create_a_bean

It makes mention of storeAll($beans) method, but I am unsure exactly how I am suppose to format the data in $beans.

I have tried googling for this and can not find anything related to bulk inserts. Maybe I have searched for the wrong terms.

I am new to this ORM, any help with would appreciated, thanks!

Answer 1

You are definitely right on track. Create a new bean using $bean=R::dispense('bean'); or multiple beans as an array $beans=R::dispense('bean',5);

Then you populate the beans with data:

$bean->title='Hello World!';
//or with an array
$beans[0]->title='Hello World!';
$beans[1]->title='Hello World! Bean 1';
//etc

Then store the bean(s):

R::store($bean);
//or
R::storeAll($beans);

All the beans must be the same type if you have multiples as far as I know, so you can do something like:

$beans=array();
$beans[]=R::dispense('bean');
$beans[]=R::dispense('bean');
$beans[0]->title='Hello World!';
$beans[1]->title='Hello World!1';
R::storeAll($beans);

I could be wrong about that though. The main thing is that this is all a typical ORM, but redbean also supports regular SQL if you need to use it. Hope that helps!

Answer 2

Some real data behind this approach. FIRST APPROACH. foreach item found

$bean = R::dispense('bean');
$bean->title = "hello";
R::store("bean");

time taken for 5660 rows = 43s on my mac

SECOND APPROACH.

$beans=array();
$beans[]=R::dispense('bean');
$beans[]=R::dispense('bean');
$beans[0]->title='Hello World!';
$beans[1]->title='Hello World!1';
R::storeAll($beans);

For 5660 rows, 46s. The storeAll is where all the time is. So its taking ages to store these beans.

THIRD APPROACH

$beans=R::dispense('bean',5560);
    
for loop
  $bean[$i]->title = "hello world";
end for
    
R::storeAll($beans);

For 5660 rows 45s. Result. None of these approaches are any quicker. : ( RedBean Transactions didn't seem to make this any quicker either

From the creator of RedBean https://stackoverflow.com/a/18811996/445492 Bulk Insert is not supported, use pure sql.

FOURTH APPROACH

for loop
  R::exec("insert into bean(title) values (1,'hello world')");
end for

for 5660 rows 7.3s <----- WOW (please note: I am actually doing some stuff prior so all these results are -4.3 seconds.)

Answer 3

Hence every bean needs to be created first and the method to create a bean is dispense

$bean = R::dispense('customers');
$bean->name = "John";
R::store($bean);
$bean->name = "Walter"
R::store($bean);

the code above creates only one bean even after storing it. Still $bean refers to the same object, so for each record you have to create a new been by using dispense method.

Luckily we have storeAll method that stores all the beans but it requires an array of beans. So we create a bean in each iteration and push it to the array and then at the end of loop we just pass that array to storeAll function.

//create empty array 
$beans = array();   

//for each customer post create a new bean as a row/record          
foreach ($post as $customer) {
     $bean = R::dispense('customers');
     //assign column values 
     $bean->firstName = $customer['first_name'];
     $bean->lastName = $customer['last_name'];
     //push row to array
     $beans[] = $bean;
 }

 //store the whole array of beans at once               
 R::storeAll($beans);

Answer 4

In the approaches 1, 2 and 3 suggested by John Ballinger, one way to optimize the run time is to put all the insertions performed by storeAll($beans) inside one database transaction. This could be done as follows: replace the line "R::storeAll($beans)" by the following three lines:

R::begin();
R::storeAll($beans);
R::commit();

This approach reduces dramatically the run time when the array $beans is large, AND is not necessary to use SQL "explicitly".