Level 5
May 31, 2020
12
Level 5
multiple foreign key indicating one primary key and getting values from table
guys iam working with a project quiz examination.
before writing the exam assessment must be created.
i have a for to create assessment. in that
assessment name:
for which class: class_id (referencing class table)
which subject: subject_id (referencing subject table)
which chapter: chapter_id (referencing chapter table) -> this is the matter. i have multiple select in form. more than one chapter can be selected
how this is possible
for single select i have table structure like this
id assmnt_name class_id subject_id chapter_id
1 Asmnt1 1 1 1
for this i get get the eloquent from DB easyily. just passing where condition of class_id, subject_id, chapter_id
but my client expecting multiple chapter selection. how can i make my table structure??
and after that how can i get it from DB say for example if 3 chapters are selected means so getting question from question table where chpater_id=1,2,3.
how this is possible?
Level 73
You should use a whereIn clause something like
Model::whereIn('foreign_id1' $arrayOfids)->whereIn('foreign_id2', $arrayOfOtherIds)->get();
Level 5
@tray2 thank you so much for your response.
actually how i can store multiple values for a single foreign key in table?? i think i need to make a sub table am i right???
first how i need to store then how i need to fetch it from db??
Level 73
Not sure what you mean here but if you show some code and table migrations it will be easier to understand what it is you are trying to do.
Level 5
@tray2 sorry. wait i will show the tables.
i have a table AssessmentTable with columns
id name subject_id(foreign key)
1 A1 1
2 A2 2
actually the user selects only one subject from the drop down. so that subject_id is saved in AssessmentTable.
But my client expecting to choose multiple subjects from form. so how i can store multiple subject_id in this table??
that is A1 itself has multiple subect_id
i think u understood. i think i need to have a sub table for this assessment A1 - > to store multiple subject_id am i right??
Level 73
Yes you can. You just loop over the selection array and store them one by one.
Level 5
@tray2 thank you for you respose.
so i need to create a sub table for assessment and store the subject_id there if it has multiple value am i right??
ok i know to loop over it and will store it in table thats fine. ext how i can get the values from table for multiple values of foreign key
say for example
Where('subject_id', array of subject id's) how this can be achieved??
Level 73
Whitout knowing your existing table structure it'shad to giver you advise on how to do it.
Level 5
my assessment table migration
$table->bigIncrements('id');
$table->bigInteger('login_user_id')->unsigned();
$table->foreign('login_user_id')->references('id')->on('users')->onDelete('cascade');
$table->string('assessment_name');
$table->bigInteger('request_id')->unsigned();
$table->foreign('request_id')->references('id')->on('request_assessments')->onDelete('cascade')-
>nullable();
$table->bigInteger('school_id')->unsigned();
$table->foreign('school_id')->references('id')->on('school_registrations')->onDelete('cascade')->nullable();
$table->bigInteger('class_id')->unsigned();
$table->foreign('class_id')->references('id')->on('class_details')->onDelete('cascade')->nullable();
$table->bigInteger('subject_id')->unsigned();
$table->foreign('subject_id')->references('id')->on('subject_details')->onDelete('cascade')->nullable();
$table->date('exam_date')->nullable();
$table->double('timeduration')->nullable();
$table->string('assessment_status')->nullable();
in this subject_id is my question..
subject_id is foreign key. for this subject_id i need mutiple values?? how that is possible??
Level 73
So you are talking about a many to many relation then you need to use a pivot table that contains the subject_id and the assessment_id.
https://laravel.com/docs/7.x/eloquent-relationships#many-to-many
Level 5
@tray2 well thank you i understood. this is what my confusion. thank youuuu..
if possible kindly visit these links pleasee..
Level 5
@tray2 i have another small doubt,
i will create a pivot table lie this
assessment_details_subject_details with columns
id assessment_id subject_id
1 1 1
2 1 2
3 1 3
so here for assessment_id 1 it has three subjects. ok fine. Now whats my query i have a questionDetails table with columns
id subject_id chapter_id q.no ques_description
1 1 1 Q1 ----
2 1 1 Q2 ---
3 2 1 Q3 ---
``
so iam expecting a query like this
fetch questions from question table where subject_id = subject_ids given in the pivot table for that specific assessment```assessment_details_subject_details```
i think you understood the question.kindly reply for this please,
Please or to participate in this conversation.