3

I have a table named 'categories' with uniqueness constraint on 'category_name' in my application, I want to inset multiple rows in 'categories'.

Bad Solution:

foreach($categories as $category) {
   Category::firstOrCreate(array('category_name' => $category['name']));
}

This can be a solution but this is not a good one.The issue is when there will be hunders or thousands of record, this will make a lot of queries to database. which is not good.

Comparativel berter Solution

foreach($categories as $category){
    $cats[] = array('category_name' => $category['name']);
}
Category::insert($cats);

But when I try to insert a duplicate 'category_name', it throws an exception and none of category name is inserted.

I know that we can use INSERT IGNORE but I'm looking for some build in laravel solution.

Muhammad Saud
  • 356
  • 3
  • 13

1 Answers1

3

I think it won't be possible because in 2nd case one query will be executed so even if you catch any error no data will be inserted because SQL server refuse it because of unique constraint. I don't think Laravel can handle it.

Probably something like this is compromise to solve it quicker:

foreach($categories as $category){
   $names[] = $category['name']
}

$dups = Category::whereIn('name', $names)->lists('name');

if ($dups) {
  // here you can log or display message that some categories won't be inserted
}

$namesIns = [];
$cat = [];
foreach($categories as $category){
    $item = ['category_name' => $category['name']];
    if (!in_array($category['name'], $dups) && !in_array($item, $cat)) {
      $cat[] = $item;
      $namesIns[] = $category['name'];
    }
}
Category::insert($cat);

$ids = Category::whereIn('name', $namesIns)->lists('id');
Marcin Nabiałek
  • 109,655
  • 42
  • 258
  • 291
  • `lists` is what you want instead of `pluck`, which returns only single row. – Jarek Tkaczyk Oct 26 '14 at 11:50
  • Thanks, this worked for me. But can we get ids of new inserted rows? – Muhammad Saud Oct 27 '14 at 03:57
  • @MuhammadSaud You could get them if you inserted records one by one using `Category::create` inside loop. Otherwise using `insert` method I think you cannot get them. Of course you could run another query to get ids in your database for data you inserted – Marcin Nabiałek Oct 27 '14 at 07:08
  • Yes, but loop will create issues when inserting too many rows. Running another query to get ids seems a better solution. Can you tell me which query will return the ids of the last insertion? – Muhammad Saud Oct 27 '14 at 09:17
  • @MuhammadSaud I've updated my code, in `$ids` variable you should have array of inserted ids – Marcin Nabiałek Oct 27 '14 at 09:20
  • @MarcinNabiałek Sorry for the delay, This works perfectly fine for me. Thanks for your help. :) – Muhammad Saud Oct 29 '14 at 17:23