Love Fuel?    Donate

FuelPHP Forums

Ask your question about FuelPHP in the appropriate forum, or help others by answering their questions.
Is there a way to use where in an INSERT clause to check for NOT EXISTS
  • bperinbperin
    I have this piece of code that looks to insert "category" models into the database, as it is now I have to check to see if the id exists and then if not go ahead and build a new model, this means an extra query. It would be easy if I could use the INSERT WHERE NOT EXISTS (...) But I don't think there is a way to do this

    Right now this is the code 

     foreach ($_categories as $category_item) {



                                              $cid = $category_item['id'];
                                              $name = $category_item['name'];

                                              $check_category = \DB::select('id')
                                                          ->from('categories')
                                                          ->where('id', '=', $cid)
                                                          ->limit(1)
                                                          ->execute();

                                              if (!$check_category->current()) {
                                                    \DB::insert('categories')
                                                                ->set(array(
                                                                    'id' => $cid,
                                                                    'name' => $name
                                                                ))
                                                                ->execute();
                                              }

                                              \DB::insert('place_categories')
                                                          ->set(array(
                                                              'place_id' => $insert_id,
                                                              'category_id' => $cid
                                                          ))
                                                          ->execute();
                                        }
  • philipptempelphilipptempel
    @bperin: What you're looking for is already implemented in MySQL and is called "REPLACE INTO" which basically performs an "INSERT INTO (...) WHERE NOT EXISTS (...)".

    However, I'm not sure you can do so with the ORM neither with \Fuel\Core\Database as I get from looking at the code since there's no ```\DB::replace()``` method - or similar. That's probably because this feature is heavily depending on the backed being used, yet you may consider creating a feature or pull request.
Add a Comment

Howdy, Stranger!

It looks like you're new here. If you want to get involved, click one of these buttons!

Sign In Apply for Membership

Categories

In this Discussion