CodeIgniter Forums
Can I make this query using the active record class? - Printable Version

+- CodeIgniter Forums (https://forum.codeigniter.com)
+-- Forum: Archived Discussions (https://forum.codeigniter.com/forum-20.html)
+--- Forum: Archived Development & Programming (https://forum.codeigniter.com/forum-23.html)
+--- Thread: Can I make this query using the active record class? (/thread-43298.html)



Can I make this query using the active record class? - El Forum - 07-06-2011

[eluser]bigmike7801[/eluser]
I was using the active record class until I had to add a sub query.

Here's my query:

Code:
SELECT job_id, jobs.user_id, title, description, commission_min, commission_max, skills, post_date, upgrade_border, upgrade_highlight, profiles.display_name, COALESCE(avg_score, 0) as avg_score
FROM jobs
JOIN profiles ON(jobs.user_id = profiles.user_id)
JOIN job_categories ON(jobs.cat_id = job_categories.cat_id)
JOIN job_sub_categories ON(jobs.sub_cat_id = job_sub_categories.sub_cat_id)
LEFT OUTER JOIN (SELECT employer_id, AVG((performance + quality + availability + communication)/4) AS avg_score
    FROM feedback GROUP BY employer_id) AS sq
    ON(jobs.user_id = sq.employer_id)
WHERE status = 'open'
LIMIT 5

Thanks for any help!