Average rating then join 2 tables using codeigniter

Question

I need some help, I have 2 table, like this

     table a                      table b
+-------+---------+       +---+--------+--------+
| no    |   name  |       | NO| cafe   | rating | 
+-------+---------+       +---+--------+--------+
|   1   | cafe a  |       | 1 | cafe a |   5    |
|   2   | cafe b  |       | 2 | cafe a |   4    |
|   3   | cafe c  |       | 3 | cafe b |   4    |
                          | 4 | cafe b |   3    |
                          | 5 | cafe a |   2    |

the results I want

+-------+---------+-------+
| no    |   name  |rating | 
+-------+---------+-------+
|   1   | cafe a  | 4.5   | 
|   2   | cafe b  | 3.5   | 
|   3   | cafe c  |   2   | 

can someone show me how to solve it using codeigniter, or give a reference for models. thanks

Answer 1

You seem to want a join and aggregation:

select a.*, b.rating
from (select name, avg(b.rating) as rating
      from b
      group by name
     ) b join
     a
     on a.name = b.name;

Note that some databases do integer averaging, so you might need avg(b.rating * 1.0).