Ggtcf

Do I have to take mana from my deck or hand when tapping this card?

categorizing a variable turns it from insignificant to significant

Can a Knock spell open the door to Mordenkainen's Magnificent Mansion?

Magnifying glass in hyperbolic space

What properties make a magic weapon befit a Rogue more than a DEX-based Fighter?

Why do Radio Buttons not fill the entire outer circle?

What do the positive and negative (+/-) transmit and receive pins mean on Ethernet cables?

Relations between homogeneous polynomials

How to preserve electronics (computers, ipads, phones) for hundreds of years?

"Marked down as someone wanting to sell shares." What does that mean?

A seasonal riddle

Why doesn't Gödel's incompleteness theorem apply to false statements?

Weird lines in Microsoft Word

How do I lift the insulation blower into the attic?

Extract substring according to regexp with sed or grep

Output visual diagram of picture

C++ lambda syntax

Highest stage count that are used one right after the other?

When is the exact date for EOL of Ubuntu 14.04 LTS?

Is there any common country to visit for persons holding UK and Schengen visas?

Why can't I get pgrep output right to variable on bash script?

Why is implicit conversion not ambiguous for non-primitive types?

"Oh no!" in Latin

What is the tangent at a sharp point on a curve?

Laravel - Model creating with subquery

Laravel 4 Eloquent ORM advance relationshipsManaging relationships in Laravel, adhering to the repository patternLaravel getting id from stored objectLaravel row creation fillableTracking database changes on related models… Laravel 5.1Laravel 5.3 Creating Models Returns “Field doesn't have a default value”Error creating related model instances in Laravel 5.3Laravel Virtual Columns fail to saveUse subqueries to assign column values for each record in laravelLaravel - Set model attribute as Raw SQL on creating

0

I have an items table that has a sequence column which its value should be the result of a subquery:

INSERT INTO items (company_id, sequence, name)
 VALUES (1, (
 SELECT
 COALESCE(max(i.sequence), 0) + 1
 FROM
 items AS i
 WHERE
 company_id = 1), 'item 1 of company 1')

But in Laravel I'm using Eloquent mass assignment with create method to insert my items records:

$company->items()->create($inputs);

How to define the model sequence attribute as a raw subquery on creating records via mass assignment?

php mysql laravel

share|improve this question

asked Mar 7 at 20:22

Alexandre ThebaldiAlexandre Thebaldi

2,23511428

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  I'm not sure the error is related to what you're asking. Mass assignment is related to one of the fields being guarded, or not fillable, in your model definition. In regards to what you're asking, what does $inputs contain?
  
  – Devon
  Mar 7 at 20:42
  
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  @Devon $inputsis the array of fillable attributes. I just want a way to set the sequence attribute as a raw SQL (like the subquery in the example)
  
  – Alexandre Thebaldi
  Mar 7 at 20:50
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  @Devon is not mandatory to make sequence as fillable if there is another way to set his value as subquery.
  
  – Alexandre Thebaldi
  Mar 7 at 20:51
  

  
  
  
  

add a comment | 

0

I have an items table that has a sequence column which its value should be the result of a subquery:

INSERT INTO items (company_id, sequence, name)
 VALUES (1, (
 SELECT
 COALESCE(max(i.sequence), 0) + 1
 FROM
 items AS i
 WHERE
 company_id = 1), 'item 1 of company 1')

But in Laravel I'm using Eloquent mass assignment with create method to insert my items records:

$company->items()->create($inputs);

How to define the model sequence attribute as a raw subquery on creating records via mass assignment?

php mysql laravel

share|improve this question

asked Mar 7 at 20:22

Alexandre ThebaldiAlexandre Thebaldi

2,23511428

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  I'm not sure the error is related to what you're asking. Mass assignment is related to one of the fields being guarded, or not fillable, in your model definition. In regards to what you're asking, what does $inputs contain?
  
  – Devon
  Mar 7 at 20:42
  
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  @Devon $inputsis the array of fillable attributes. I just want a way to set the sequence attribute as a raw SQL (like the subquery in the example)
  
  – Alexandre Thebaldi
  Mar 7 at 20:50
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  @Devon is not mandatory to make sequence as fillable if there is another way to set his value as subquery.
  
  – Alexandre Thebaldi
  Mar 7 at 20:51
  

  
  
  
  

add a comment | 

I have an items table that has a sequence column which its value should be the result of a subquery:

INSERT INTO items (company_id, sequence, name)
 VALUES (1, (
 SELECT
 COALESCE(max(i.sequence), 0) + 1
 FROM
 items AS i
 WHERE
 company_id = 1), 'item 1 of company 1')

But in Laravel I'm using Eloquent mass assignment with create method to insert my items records:

$company->items()->create($inputs);

How to define the model sequence attribute as a raw subquery on creating records via mass assignment?

php mysql laravel

share|improve this question

asked Mar 7 at 20:22

Alexandre ThebaldiAlexandre Thebaldi

2,23511428

INSERT INTO items (company_id, sequence, name)
 VALUES (1, (
 SELECT
 COALESCE(max(i.sequence), 0) + 1
 FROM
 items AS i
 WHERE
 company_id = 1), 'item 1 of company 1')

$company->items()->create($inputs);

share|improve this question

asked Mar 7 at 20:22

Alexandre ThebaldiAlexandre Thebaldi

2,23511428

share|improve this question

asked Mar 7 at 20:22

Alexandre ThebaldiAlexandre Thebaldi

2,23511428

asked Mar 7 at 20:22

Alexandre ThebaldiAlexandre Thebaldi

2,23511428

asked Mar 7 at 20:22

Alexandre ThebaldiAlexandre Thebaldi

2,23511428

2 Answers
2

active

oldest

votes

1

What's the likelyhood of two entries for the same company in less than a second? I'm sure it's so unlikely that you probably don't need to use a raw statement here. While this is ever-so-slightly more subject to a race condition, it greatly simplifies your problem when using Eloquent..

$inputs['sequence'] = ($company->items()->max('sequence') ?? 0) + 1;
$company->items()->create($inputs);

share|improve this answer

edited Mar 7 at 21:21

answered Mar 7 at 21:07

DevonDevon

23.4k42747

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  two statements running on database... not good
  
  – Alexandre Thebaldi
  Mar 7 at 21:09
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Explain what's not good..
  
  – Devon
  Mar 7 at 21:09
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  1. select max... 2. insert into... you can resume this into only one statement
  
  – Alexandre Thebaldi
  Mar 7 at 21:10
  
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  It's the same query as what you have, just not run as a subquery... This also prevents you from having to do a refresh().
  
  – Devon
  Mar 7 at 21:11
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  yes, looking for the downside of doing refresh this approach makes sense.
  
  – Alexandre Thebaldi
  Mar 7 at 21:12
  

  
  
  
  

 | 
show 3 more comments

1

I believe you will have two ways to go about this, either using model events on the items model to set that sequence field anytime the model is saved, or combining your $inputs array with the result of the sub query.
The first option, using model events. You just add the event listener in the Items model:

protected static function boot()
 static::saving(function($thisModel)
 $thisModel->attributes['sequence'] = your sub query;
 );

This will updated the sequence any time the model is saved. This might be nice if you know that it will need to be updated any time it is saved. You can change saving to creating if this is only something that needs to be done on creation. There are a lot of other options if neither of these are what you need.

The other option would be to just have a different query added to the $inputs array.

$subQuery = your sub query, using whatever you like. 
$company->items()->create($inputs + ['sequence' => $subQuery]);

The array union operator + should be fine to use.

share|improve this answer

answered Mar 7 at 20:50

N MahurinN Mahurin

685214

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  I came into these two solutions with usage of DB::raw('subquery'). The only issue with this approach is that I don't know the side effects of setting a model attribute as subquery. So actually I'm running $model->refresh(); right after creating because else $model->sequence === subquery instead of database value...
  
  – Alexandre Thebaldi
  Mar 7 at 20:56
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  hmm, that is an interesting problem. Have you seen what a setSequenceAttribute() would do in this situation? If you called the sub query in the setter would the sequence === subquery I wonder.
  
  – N Mahurin
  Mar 7 at 20:59
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  It could be a different issue entirely, since DB::raw() always returns a standard object. Are you retrieving just the returned value you need from the object when you save it?
  
  – N Mahurin
  Mar 7 at 21:01
  

  
  
  
  

add a comment | 

Your Answer

StackExchange.ifUsing("editor", function ()
StackExchange.using("externalEditor", function ()
StackExchange.using("snippets", function ()
StackExchange.snippets.init();
);
);
, "code-snippets");

StackExchange.ready(function()
var channelOptions =
tags: "".split(" "),
id: "1"
;
initTagRenderer("".split(" "), "".split(" "), channelOptions);

StackExchange.using("externalEditor", function()
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled)
StackExchange.using("snippets", function()
createEditor();
);

else
createEditor();

);

function createEditor()
StackExchange.prepareEditor(
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader:
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
,
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
);



);

draft saved

draft discarded

Sign up or log in

StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);

Sign up using Google

Sign up using Facebook

Sign up using Email and Password

Post as a guest

Name

Email

Required, but never shown

StackExchange.ready(
function ()
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f55052224%2flaravel-model-creating-with-subquery%23new-answer', 'question_page');

);

Post as a guest

Name

Email

Required, but never shown

1

What's the likelyhood of two entries for the same company in less than a second? I'm sure it's so unlikely that you probably don't need to use a raw statement here. While this is ever-so-slightly more subject to a race condition, it greatly simplifies your problem when using Eloquent..

$inputs['sequence'] = ($company->items()->max('sequence') ?? 0) + 1;
$company->items()->create($inputs);

share|improve this answer

edited Mar 7 at 21:21

answered Mar 7 at 21:07

DevonDevon

23.4k42747

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  two statements running on database... not good
  
  – Alexandre Thebaldi
  Mar 7 at 21:09
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Explain what's not good..
  
  – Devon
  Mar 7 at 21:09
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  1. select max... 2. insert into... you can resume this into only one statement
  
  – Alexandre Thebaldi
  Mar 7 at 21:10
  
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  It's the same query as what you have, just not run as a subquery... This also prevents you from having to do a refresh().
  
  – Devon
  Mar 7 at 21:11
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  yes, looking for the downside of doing refresh this approach makes sense.
  
  – Alexandre Thebaldi
  Mar 7 at 21:12
  

  
  
  
  

 | 
show 3 more comments

1

What's the likelyhood of two entries for the same company in less than a second? I'm sure it's so unlikely that you probably don't need to use a raw statement here. While this is ever-so-slightly more subject to a race condition, it greatly simplifies your problem when using Eloquent..

$inputs['sequence'] = ($company->items()->max('sequence') ?? 0) + 1;
$company->items()->create($inputs);

share|improve this answer

edited Mar 7 at 21:21

answered Mar 7 at 21:07

DevonDevon

23.4k42747

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  two statements running on database... not good
  
  – Alexandre Thebaldi
  Mar 7 at 21:09
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Explain what's not good..
  
  – Devon
  Mar 7 at 21:09
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  1. select max... 2. insert into... you can resume this into only one statement
  
  – Alexandre Thebaldi
  Mar 7 at 21:10
  
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  It's the same query as what you have, just not run as a subquery... This also prevents you from having to do a refresh().
  
  – Devon
  Mar 7 at 21:11
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  yes, looking for the downside of doing refresh this approach makes sense.
  
  – Alexandre Thebaldi
  Mar 7 at 21:12
  

  
  
  
  

 | 
show 3 more comments

What's the likelyhood of two entries for the same company in less than a second? I'm sure it's so unlikely that you probably don't need to use a raw statement here. While this is ever-so-slightly more subject to a race condition, it greatly simplifies your problem when using Eloquent..

$inputs['sequence'] = ($company->items()->max('sequence') ?? 0) + 1;
$company->items()->create($inputs);

share|improve this answer

edited Mar 7 at 21:21

answered Mar 7 at 21:07

DevonDevon

23.4k42747

$inputs['sequence'] = ($company->items()->max('sequence') ?? 0) + 1;
$company->items()->create($inputs);

share|improve this answer

edited Mar 7 at 21:21

answered Mar 7 at 21:07

DevonDevon

23.4k42747

answered Mar 7 at 21:07

DevonDevon

23.4k42747

answered Mar 7 at 21:07

DevonDevon

23.4k42747

1

I believe you will have two ways to go about this, either using model events on the items model to set that sequence field anytime the model is saved, or combining your $inputs array with the result of the sub query.
The first option, using model events. You just add the event listener in the Items model:

protected static function boot()
 static::saving(function($thisModel)
 $thisModel->attributes['sequence'] = your sub query;
 );

This will updated the sequence any time the model is saved. This might be nice if you know that it will need to be updated any time it is saved. You can change saving to creating if this is only something that needs to be done on creation. There are a lot of other options if neither of these are what you need.

The other option would be to just have a different query added to the $inputs array.

$subQuery = your sub query, using whatever you like. 
$company->items()->create($inputs + ['sequence' => $subQuery]);

The array union operator + should be fine to use.

share|improve this answer

answered Mar 7 at 20:50

N MahurinN Mahurin

685214

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  I came into these two solutions with usage of DB::raw('subquery'). The only issue with this approach is that I don't know the side effects of setting a model attribute as subquery. So actually I'm running $model->refresh(); right after creating because else $model->sequence === subquery instead of database value...
  
  – Alexandre Thebaldi
  Mar 7 at 20:56
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  hmm, that is an interesting problem. Have you seen what a setSequenceAttribute() would do in this situation? If you called the sub query in the setter would the sequence === subquery I wonder.
  
  – N Mahurin
  Mar 7 at 20:59
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  It could be a different issue entirely, since DB::raw() always returns a standard object. Are you retrieving just the returned value you need from the object when you save it?
  
  – N Mahurin
  Mar 7 at 21:01
  

  
  
  
  

add a comment | 

1

I believe you will have two ways to go about this, either using model events on the items model to set that sequence field anytime the model is saved, or combining your $inputs array with the result of the sub query.
The first option, using model events. You just add the event listener in the Items model:

protected static function boot()
 static::saving(function($thisModel)
 $thisModel->attributes['sequence'] = your sub query;
 );

This will updated the sequence any time the model is saved. This might be nice if you know that it will need to be updated any time it is saved. You can change saving to creating if this is only something that needs to be done on creation. There are a lot of other options if neither of these are what you need.

The other option would be to just have a different query added to the $inputs array.

$subQuery = your sub query, using whatever you like. 
$company->items()->create($inputs + ['sequence' => $subQuery]);

The array union operator + should be fine to use.

share|improve this answer

answered Mar 7 at 20:50

N MahurinN Mahurin

685214

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  I came into these two solutions with usage of DB::raw('subquery'). The only issue with this approach is that I don't know the side effects of setting a model attribute as subquery. So actually I'm running $model->refresh(); right after creating because else $model->sequence === subquery instead of database value...
  
  – Alexandre Thebaldi
  Mar 7 at 20:56
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  hmm, that is an interesting problem. Have you seen what a setSequenceAttribute() would do in this situation? If you called the sub query in the setter would the sequence === subquery I wonder.
  
  – N Mahurin
  Mar 7 at 20:59
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  It could be a different issue entirely, since DB::raw() always returns a standard object. Are you retrieving just the returned value you need from the object when you save it?
  
  – N Mahurin
  Mar 7 at 21:01
  

  
  
  
  

add a comment | 

I believe you will have two ways to go about this, either using model events on the items model to set that sequence field anytime the model is saved, or combining your $inputs array with the result of the sub query.
The first option, using model events. You just add the event listener in the Items model:

protected static function boot()
 static::saving(function($thisModel)
 $thisModel->attributes['sequence'] = your sub query;
 );

This will updated the sequence any time the model is saved. This might be nice if you know that it will need to be updated any time it is saved. You can change saving to creating if this is only something that needs to be done on creation. There are a lot of other options if neither of these are what you need.

The other option would be to just have a different query added to the $inputs array.

$subQuery = your sub query, using whatever you like. 
$company->items()->create($inputs + ['sequence' => $subQuery]);

The array union operator + should be fine to use.

share|improve this answer

answered Mar 7 at 20:50

N MahurinN Mahurin

685214

protected static function boot()
 static::saving(function($thisModel)
 $thisModel->attributes['sequence'] = your sub query;
 );

$subQuery = your sub query, using whatever you like. 
$company->items()->create($inputs + ['sequence' => $subQuery]);

share|improve this answer

answered Mar 7 at 20:50

N MahurinN Mahurin

685214

answered Mar 7 at 20:50

N MahurinN Mahurin

685214

answered Mar 7 at 20:50

N MahurinN Mahurin

685214