Running raw sql inside Model : Laravel

Question

I am using Laravel and need to run raw sql query from a Model/Repository class

INSERT INTO calendar 
(room_id, date, default_count, default_price) 
VALUES ('1', '2017-01-02', '2', '400004') 
ON DUPLICATE KEY UPDATE 
default_count = VALUES(default_count), default_price = VALUES(default_price);

ex. When I insert data from UserRepository

$this->users->insert(['email' => 'john@example.com', 'votes' => 0]);

I need some kind of method to get DB connection and run sql via model

//Something like
$this->users->execute($sql);

I saw Laravel has updateOrInsert() method but I need to run this for multiple data sets at once.

How can I run raw sql query via the model class or repository?

Thanks

UPDATED - SOLVED

I went through Eloquent Model Sourcecode and found I can get connection from getConnection() method

$this->users->getConnection()->statement($sql);

Please clarify your specific problem or add additional details to highlight exactly what you need. As it's currently written, it’s hard to tell exactly what you're asking. — Zakaria Acharki, Dec 08 '16 at 11:09
You don't **need** that. What you need is to catch an exception and check if it's code contains `23000` which is the code for duplicate key entry. At that point you can simply proceed to update your model. You can create a single method in your model which deals with inserting / catching the exception / checking whether code is 23000 and then updates your model. You can avoid fetching the connection, dealing with transaction and all other nasty things you want to do. — Mjh, Dec 08 '16 at 12:20

Komal · Answer 1 · 2016-12-08T11:35:15.170

Do like this

$query = array(['room_id' => 1, 'date' => '2/1/2017', 'default_count' => '2', 'default_price' => '400004'],
['room_id' => 2, 'date' => '2/1/2017', 'default_count' => '3', 'default_price' => '455004']);


 DB::table('calendar')->insart('query');

And also you can do like this, Create obj of your modal and assign values

$obj_calendar = new Calendar();
$obj_calendar->room_id = 1;
$obj_calendar->date = date('Y-m-d H:i:s', strtotime('2017-01-02'));
$obj_calendar->default_count = 2;
$obj_calendar->default_price = 400004;
$obj_calendar->save();

Using transaction

// begin transaction

public function beginTransaction()
{
  DB::beginTransaction();
}

// make transaction

public function makeTransaction($query)
{
  DB::transaction(function($query) use($query)
  {
    DB::insert($query);
  });
}

// commit transaction

public function commitTransaction()
{
  return DB::commit();
}

In your method

public function insert($data)
{
  $this->beginTransaction();

  for($i=0; $i<count($data); $i++)
  {
    $name =  $data[$i]->first_name, "middle_name" => $data[$i]->middle_name, "last_name" => $data[$i]->last_name;

    $query = "INSERT INTO tbl_name(id, first_name, description, created_at, updated_at) VALUES (" . $data[$i]->id . ", " . $name . ", '" . $data[$i]->description . "', '" . date("Y-m-d H:i:s",strtotime($data[$i]->created_at)) . "', '" . date("Y-m-d H:i:s",strtotime($data[$i]->created_at)) . "'); ";

    $scraper_service->makeTransaction($query);
  }

  //Add last commit 
  $this->commitTransaction();
}

Sorry I need to run row sql and prefer not to load another class to get connection inside model — Dumindu Madunuwan, Dec 08 '16 at 11:25
I need to run https://github.com/laravel/framework/blob/43637b796f1ac0b1a960984231251ff04b438006/src/Illuminate/Database/Eloquent/Model.php#L605-L612 but for multiple data set. Performancewise it's better to run one query instead 10 queries for the same task — Dumindu Madunuwan, Dec 08 '16 at 11:37

score 0 · Accepted Answer · answered Dec 08 '16 at 12:43

I went through Eloquent Model Sourcecode and found I can get connection from getConnection() method

$this->users->getConnection()->statement($sql);

don't know statement() is the best way to run this sql. However it worked.

Thanks everyone for help. Also if you have a better solution please let me know

Running raw sql inside Model : Laravel

2 Answers2