36

Is it possible to update a timestamp (besides updated_at) and increment a column in one query? I obviously can

->increment('count')

and separately

->update(['last_count_increased_at' => Carbon::now()])

but is there an easy way to do both together.

Product::where('product_id', $product->id)
    ->update(['count'=> $count + 1, 'last_count_increased_at' => Carbon::now()];

Without having to query and get the count first?

Alex Harris
  • 6,172
  • 2
  • 32
  • 57

3 Answers3

70

You can specify additional columns to update during the increment or decrement operation:

Product::where('id',$id)
->increment('count', 1, ['increased_at' => Carbon::now()]);

It is more eloquent solution.

Ali Sharifi Neyestani
  • 4,162
  • 1
  • 14
  • 22
66

You can use the DB::raw method:

Product::where('product_id', $product->id)
    ->update([
      'count'=> DB::raw('count+1'), 
      'last_count_increased_at' => Carbon::now()
    ]);
Alex Harris
  • 6,172
  • 2
  • 32
  • 57
aynber
  • 22,380
  • 8
  • 50
  • 63
  • Works great, can't believe I didn't think of that, was about to do the whole thing raw. – Alex Harris Jun 06 '16 at 20:24
  • What if we don't have a matching record? How to create if no record found, without having to query and get the count first? – Ajjay Arora Apr 28 '20 at 02:20
  • 1
    @AjjayArora While that really ought to be a new question, I'm not sure that can be done in a one-liner using just Eloquent. I think you actually would have to check if the product_id exists first. – aynber Apr 28 '20 at 11:22
  • @aynber Thanks pal for replying. – Ajjay Arora Apr 29 '20 at 10:57
6

With Laravel 8 you can now achieve this in a single query to create or update on duplicate key.

$values = [
    'name' => 'Something',
    'count' => 1,
];
$uniqueBy = ['name'];
$update = ['count' => DB::raw('count+1')];

Model::upsert($values, $uniqueBy, $update);

If the model exists count will be incremented, if it is inserted count will equal 1. This is done on the DB level, so only one query involved.

Read more about upserts: https://laravel.com/docs/8.x/eloquent#upserts

madsem
  • 71
  • 2
  • 8
  • 1
    Upsert had another purpose you can use update for this and you'll be fine. – Steve Moretz Aug 10 '21 at 12:15
  • Update works for one model, the above works for multiple model values and/or a single one and increment count for each. I see this can be useful and accomplishes everything in 1 query as well. – madsem Aug 16 '21 at 09:35
  • No update can also work for multiple rows of the table you're referring to the update method on the model not the update method of the query builder. – Steve Moretz Aug 16 '21 at 11:25
  • Yes, but not with different values? So I still think this can be valuable depending on use case. I for example posted this after I had exactly this case, update many with different values and set visit count to +1 – madsem Aug 19 '21 at 09:04
  • Sure of course I'm not implying that the upsert method is useless, but in the code that you provided above it could be changed with update and nothing will go wrong. All I'm saying is that it is unnecessary in this specific context and might mislead the future viewers. – Steve Moretz Aug 19 '21 at 09:36
  • Since the original question wasn't inserting a new record, upsert changes the behavior for this situation. However, upsert has a very large benefit over updateOrCreate in the event you do want a new record created. firstOrCreate/updateOrCreate perform two queries (one to check and one to create) and that introduces a race condition on high traffic sites. As mentioned, upsert does everything in one query which eliminates that specific risk. – Steven W Jun 09 '22 at 22:51