+- CodeIgniter Forums (https://forum.codeigniter.com)
+-- Forum: Archived Discussions (https://forum.codeigniter.com/forumdisplay.php?fid=20)
+--- Forum: Archived Development & Programming (https://forum.codeigniter.com/forumdisplay.php?fid=23)
+--- Thread: Foreach loops or complicated joins in Model? (/showthread.php?tid=55765)
Foreach loops or complicated joins in Model? - El Forum - 11-08-2012
[eluser]vinofilus[/eluser]
This application will track people who hold 1 or 2 jobs in a business, and each job has multiple training requirements, and each training course has several specific rules. My tables (simplified for this example):
Regular Tables:
Staff: id, fname, lname
Jobs: id, job_name (ie cook, maintenance, registrar)
Course: id, course (union intro, food handling, sterilization)
Rules: id, rule (require when hired, refresh every year, every 3 years)
Link Tables:
Staff_Jobs: staff_id, jobs_id
Jobs_Course: jobs_id, course_id
Course_Rules: course_id, rules_id
Some staff members hold down 2 jobs, thus the staff_jobs link table.
Each job requires up to several different courses, thus the jobs_course link table.
Each course may have several applicable rules, thus the course_rules link table.
Let’s say I want to create a report (or temporary table) showing all the information available, which might look somelthing like this:
Fname lname | Job | Course | Rules
Bob Smith | Cook | Union Intro | When Hired
Bob Smith | Cook | Food Handling | When Hired
Bob Smith | Cook | Food Handling | Every year
Bob Smith | Registrar | Union Intro | When Hired
Anne Bedier | Maintenance | Union Intro | When Hired
Anne Bedier | Maintenance | Sterilization | When Hired
Anne Bedier | Maintenance | Sterilization | Every 3 years
This will be done in codeigniter, and it seems to me I have 2 options:
1) A fancy set of joins, possibly needing full outer or even a cross join?
2) Foreach loops in the model, like:
For each staff, look up the jobs they do
For each job, look up related courses
For each course, get related rules
Write out a line of fname, lname, job, course, rule to temp table (or save to an array)
Once I have that one big table or array created, I can compare that to a log file of what has already been completed and quickly generate all the reports and graphs I need, such as who is behind on taking required courses, which courses are most missed, which job has the best record, and so on.
Option 2 seems easier to understand logically, though I’m not yet sure how I’d do that in the model, specifically how I'd pass all that data to an array properly.
Option 1 may be the more appropriate database way to do it, but complex joins make my brain hurt.
I’m not sure which would be more efficient.
I’m looking for suggestions, recommendations, helpful comments, or even code examples to get me unstuck.
Thanks,
Les…
Foreach loops or complicated joins in Model? - El Forum - 11-08-2012
[eluser]Aken[/eluser]
Doesn't sound like you need any complicated joins, you'll just need a bunch of them. A single query should work fine (and typically be faster, depending on the amount of data and how normalized the DB is).
Foreach loops or complicated joins in Model? - El Forum - 11-09-2012
[eluser]vinofilus[/eluser]
I think it's pretty well normalized, which I suppose is part of the reason I have to go through so many steps to get a comprehensive table out of it. I'll keep banging my head at a query without enough joins to make that work, but I've been trying that for a some time now and came up empty... which I guess is why I figured I needed to resort to some sort of loop.
Admittedly, any time I did a multi level loop in my model, the passing of arrays back to the controller got kind of messy, so the notion that I could do this in one query is very appealing.
I'm hoping someone can help get me past my stuckness with this step.
Thanks for your reply. While I'm still stuck, it gives me hope.
Les...
Foreach loops or complicated joins in Model? - El Forum - 11-09-2012
[eluser]vinofilus[/eluser]
Oops. My bad.
What yesterday seemed impossible, today, with a fresh start and a new cup of espresso was suddenly so easy.
I got that query going nicely.
I guess sometimes it's the process of carefully stating the issue, and having someone like Aken just redirect or confirm the right direction to go.
Thanks Aken!
Les...