How to integrate feedback score into search results

I am currently running the following query that returns a set of jobs along with their categories and the user name of the person who posted it.

SELECT job_id, user_id, title, profiles.user_name
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_categorie开发者_C百科s ON jobs.sub_cat_id = job_sub_categories.sub_cat_id 
WHERE `status` = 'open' 
ORDER BY post_date desc 
LIMIT 5

I have a table called feedback that holds rows of feedback for a particular employer based on their previous transactions (much like ebay).

feedback_id|employer_id|job_id|performance_score|quality_score|availability_score|communication_score

What I want to be able to do is to sort and filter my results based on an employers current feedback rating and I'm not sure how to add this into my query. It seems like I have to do some math within my query or run a sub-query perhaps?. Or should I modify my feedback table to include another field such as total feedback given for a particular rating?

Any help would be greatly appreciated.

Rating is calculated at all of the feedback scores added together divided by the number of rows then divided by the number 4 because there are 4 scored fields (performance, quality, availability and communication) so feedback_avg = (feedback_total/num_rows)/4

---

Let me give it a shot. I will assume you have only two tables, employers: [id, name] and feedback: [id, employer_id, score].

First off, the score subquery:

SELECT employer_id, SUM(score) AS total_score, COUNT(*) AS num_rows
       FROM feedback GROUP BY employer_id;

Now the main query:

SELECT name, total_score/num_rows AS avg_score
       FROM employers JOIN ([subquery]) AS sq ON(employers.id = sq.employer_id)
       WHERE avg_score > 0.5;

Paste the entire subquery into the indicated position.

---

Tip: Views

If you like, you can make the sub-query a permanent view, and use that in the main query:

CREATE VIEW score_tally AS
SELECT employer_id, SUM(score) AS total_score, COUNT(*) AS num_rows
       FROM feedback
       GROUP BY employer_id;

SELECT name, total_score/num_rows AS avg_score
       FROM employers JOIN score_tally ON(employers.id = score_tally.employer_id)
       WHERE avg_score > 0.5;

---

Tip (again): The above tip was stupid, we should use the built-in AVG:

CREATE VIEW score_tally AS
SELECT employer_id, AVG(score) AS avg_score
       FROM feedback
       GROUP BY employer_id;

SELECT name, avg_score
       FROM employers JOIN score_tally ON(employers.id = score_tally.employer_id)
       WHERE avg_score > 0.5;

---

Let's guess what your complete query might look like:

SELECT job_id,
       user_id,
       title,
       profiles.user_name AS user_name,
       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)
            JOIN (SELECT employer_id, AVG(score) AS avg_score FROM feedback GROUP BY employer_id) AS sq
                 ON(employers.id = sq.employer_id)

        WHERE status = 'open' AND avg_score > 0.5

        ORDER BY post_date desc
        LIMIT 5