Finding & comparing array values via Eloquent - all help appreciated!

Hello folks, I’m looking for some help on adding and searching arrays for a project I’m working on.

This is for a training system which I’ve built but now what I need to do is this: when the admin is creating a course, the only instructors which should be returned as selectable are those with the correct qualification. Now they may need to have more than one qualification so I’m thinking of adding a column ‘qualifications’ to the course and instructor and storing the IDs of each qualification so I can later tally them up.

So, for example, my (simplified) tables would look like this:

Qualifications table: [id, name (string)]
ID 1: ‘First Aid’
ID 2: ‘Health and Safety’
ID 3: ‘Food Hygiene’
etc

Instructors table: [id, name (string), qualifications (array)]
ID 1: ‘Fred’ - ‘1’
ID 2: ‘Bob’ - ‘2, 3’
ID 3: ‘Mary’ - 1, 2’
etc

Course: [id, name (string), qualifications (array)]
ID 1: ‘Food hygiene’ - ‘2, 3’ (health and safety, food hygiene qualifications)
ID 2: ‘First aid’ - ‘1, 2’ (first aid, health and safety)
ID 3: ‘Health and Safety’ - ‘2’ (health and safety)
etc

Now, when I’m showing my ‘food hygiene’ course (ID 1), I need to only return Bob since he’s the only instructor with both qualifications.

When I show my ‘Health and Safety’ course (ID 3), it should return both Bob and Mary since they both have the health and safety qualification. Fred doesn’t appear on either of these as he only has a first aid qualification.

The parts I’m not sure about is how to efficiently do the following:

1 I’m adding a qualification to an instructor from a html table where each row displays the qualification name and a button (which will send the ID via ajax to the controller). I know how to send the ajax call, however I don’t understand how I add each qualification ID to the existing array in the controller.

For example, I want Fred to have a Food Hygiene certificate so I need to add ID 3 to his qualification column which currently only has ID 1.

In my controller I’d be doing something like:

$addqualification = Instructor::where(‘id’,1)->update(‘qualifications’, *WHAT DO I USE HERE TO ADD TO AN EXISTING ARRAY?*);

2 I’m now displaying a course. I need to only display the instructors who have the correct qualification(s). In the health and safety course I need to return Bob and Mary as they both have qualification ID 2.

So, my controller would be something like:

// Find the course
$course = Course::find(3);

// Grab the array of qualifications an instructor must have
$qualification_array = $course->qualifications; // this would get the array of course ids which the instructors must have, in this case qualification ID 2 (health and safety)

$instructors = Instructor:: *?*

This is the bit I’m really stuck on!

I need to say:

Grab the course qualifications array from the courses table and…
…using the IDs in that array…
…find all instructors from the instructors table…
…who have the all matching qualification IDs in their qualifications array

Again, I need to check that each instructor has all qualifications required for the course, not just one (so the food hygiene course would only return Bob since he has both qualifications).

Any help on this would be most appreciated, I've only just got to grips with working with arrays but its this find/comparison part which has got me completely stumped!

jlrdw

8 years ago

Level 75

If your Qualifications table isn't too large, i.e., just a few items you could use checkbox for the Qualifications.

A linked table could have

name  :  qual_id  :  qualified
bob     :    1          :   0
bob     :    2         :   1
bob     :    3         :   1

where col 3 is the true/false.

Or as you are doing store like

ID 2: ‘Bob’ - ‘2, 3’

and use the ‘2, 3’ as a simple array. A better delimiter than comma is a pipe symbol o

ID 2: ‘Bob’ - ‘2|3’

Then you can use explode http://php.net/manual/en/function.explode.php

idcreatv

8 years ago

Best Answer

Level 3

Ah, I see. Thanks for the pointer @jlrdw .

I did a very quick test and was able to come up with this:

/* Start comparison of instructor qualifications*/
    // Get the course qualifications (stored as string)
    $course = DB::table('array_test')->where('id',38)->get()->pluck('test_array');
    // Get the instructor qualifications (stored as string)
    $instructor = DB::table('array_test')->where('id',39)->get()->pluck('test_array');
    // Turn course array into string
    $courseQualifications = explode(',',$course[0]);
    // Turn instructor array into string
    $instructorQualifications = explode(',',$instructor[0]);
    $results = !array_diff($courseQualifications, $instructorQualifications);
    // Dump result to screen
    dd($results);
/* End */

$course id (#38) would be the course ID, $instructor id (#38) would be the instructor ID and I'd access them in the view and run a quick compare.

If all of the course qualification IDs are found in the instructors qualification IDs it returns true, otherwise false.

I can work with this, do you think this would be okay to work with?

jlrdw

8 years ago

Level 75

Should be fine, a lot of this stuff is just "logic".

1 like

idcreatv

8 years ago

Level 3

Cheers @jlrdw, most appreciated.

Please or to participate in this conversation.