Level 13
??
Feb 23, 2017
11
Level 13
How validate unique combination?
i have pivot table course_subject, in this table combination of "course_id, subject_id" are primary key, so how can i validate unique key combination before attaching key here is my controller
public function postform(Request $request)
{
// $this->validate($request,[
// 'course_id' + 'subject_id' => 'required|unique:course_subject',
// ]);
$courses = $request->course_id;
$subjects = $request->subject_id;
$course = Course::where('id',$courses)->first();
$subject = Subject::where('id',$subjects)->first();
$course->psubjects()->attach($subject);
flash()->success('Successfully Subject Added in Course '. $course->name);
return back();
}
migration table
Schema::create('course_subject', function (Blueprint $table) {
$table->integer('course_id')->unsigned()->index();
$table->integer('subject_id')->unsigned()->index();
$table->primary(['course_id','subject_id']);
$table->timestamps();
});
Level 5
that's what a composite primary key is for: to ensure that the combination is unique
Level 3
Combination will always be unique as the database won't let you insert anything otherwise.
So it's up to you to insert those keys accodingly so that the DB won't get you primary key constraint errors.
Now, next up is how you insert them. But I need to know where does this request come from:
$courses = $request->course_id;
$subjects = $request->subject_id;
Level 13
@Mittensoff problem is that if someone by mistake make same combination then its show errors that "u already added make add another subject " here request view
<form method="post" action="/acadmic/course_subject/attach" data-parsley-validate ="">
{{ csrf_field() }}
<div class="form-group">
<label for="fees">Select Course :</label>
<select class="form-control" id="course_id" name="course_id" required="">
<option value="">--Select Course</option>
@foreach($courses as $key=>$value)
@if (Input::old('course_id') == $key)
<option value="{{ $key }}" selected>{{ $value }}</option>
@else
<option value="{{ $key }}">{{ $value }}</option>
@endif
@endforeach
</select>
</div>
<div class="form-group">
<label for="subject_id">Select Subject :</label>
<select class="form-control" id="subject_id" name="subject_id" required="">
<option value="">--Select Subject</option>
@foreach($subjects as $key=>$value)
@if (Input::old('subject_id') == $key)
<option value="{{ $key }}" selected>{{ $value }}</option>
@else
<option value="{{ $key }}">{{ $value }}</option>
@endif
@endforeach
</select>
</div>
<button type="submit" class="btn btn-primary btn-lg btn-block">Attach</button>
</form>
Level 3
Okay, in your validation something like this should work:
$this->validate($request, [
'course_id' => 'required|unique:course_subject, course_id, NULL, NULL, subject_id, ' . $request['subject_id'],
'subject_id' => 'required|unique:course_subject, subject_id, NULL, NULL, course_id, ' . $request['course_id'],
]);
When the first check is done it's only done for defined value of subject_id (you have subject_id=x in your DB and it checks course_id uniqueness for it). Same with the next line, it checks subject_id uniqueness for course_id = x.
I'm only not sure about the NULL, NULL. Tell me if it works.
You might want to check course_id and subject_id for SQL injection before checking uniqueness.
2 likes
Level 13
@Mittensoff here is errors
ErrorException in ValidatesAttributes.php line 721:
Undefined offset: 1
in ValidatesAttributes.php line 721
at HandleExceptions->handleError(8, 'Undefined offset: 1', 'C:\\laragon\\www\\gramyanchal\\vendor\\laravel\\framework\\src\\Illuminate\\Validation\\Concerns\\ValidatesAttributes.php', 721, array('segments' => array(' subject_id2'), 'extra' => array(), 'count' => 1, 'i' => 0)) in ValidatesAttributes.php line 721
at Validator->getExtraConditions(array(' subject_id2')) in ValidatesAttributes.php line 662
at Validator->getUniqueExtra(array('course_subject', ' course_id', ' NULL', ' NULL', ' subject_id2')) in ValidatesAttributes.php line 606
at Validator->validateUnique('course_id', '3', array('course_subject', ' course_id', ' NULL', ' NULL', ' subject_id2'), object(Validator)) in Validator.php line 338
at Validator->validateAttribute('course_id', 'Unique') in Validator.php line 253
at Validator->passes() in Validator.php line 278
at Validator->fails() in ValidatesRequests.php line 54
at Controller->validate(object(Request), array('course_id' => 'required|unique:course_subject, course_id, NULL, NULL, subject_id2', 'subject_id' => 'required|unique:course_subject, subject_id, NULL, NULL, course_id3')) in AttachCourseSubjectController.php line 42
at AttachCourseSubjectController->postform(object(Request))
at call_user_func_array(array(object(AttachCourseSubjectController), 'postform'), array(object(Request))) in Controller.php line 55
at Controller->callAction('postform', array(object(Request))) in ControllerDispatcher.php line 44
at ControllerDispatcher->dispatch(object(Route), object(AttachCourseSubjectController), 'postform') in Route.php line 203
at Route->runController() in Route.php line 160
Level 3
I forgot a ", ", edited code above.
Level 13
@Mittensoff my table is correct but why i got errors <
SQLSTATE[42S22]: Column not found: 1054 Unknown column ' NULL' in 'where clause' (SQL: select count(*) as aggregate from `course_subject` where `course_id` = 2 and ` NULL` <> NULL and ` subject_id` = 1)
Level 13
@Mittensoff i just updated my errors i got errors before is that
SQLSTATE[42S22]: Column not found: 1054 Unknown column ' course_id' in 'where clause' (SQL: select count(*) as aggregate from `course_subject` where `course_id` = 2 and ` NULL` <> NULL and ` subject_id` = 1)
its due to a space before "course_id" and after remove this i got errors
SQLSTATE[42S22]: Column not found: 1054 Unknown column ' NULL' in 'where clause' (SQL: select count(*) as aggregate from `course_subject` where `course_id` = 2 and ` NULL` <> NULL and ` subject_id` = 1)
Level 13
@Mittensoff thanks its working when i removed all space
Level 3
Oh yea, there's spaces in some columns, sorry I'm a bit tired.
Please or to participate in this conversation.