Retrieve last user reviewed a post

[rocks]

I am using codeigniter to build a business review.

I have two table 

 

Table biz

------------------------------------------------

id | username | bizname | city_id | created_at 

------------------------------------------------

 

Table review

------------------------------------------------

id | bizid | city_id | username | content | created_at

 

So, when a user reviews a business, that business shows on home page(homepage shows only businesses with reviews)

 

I want to query the last username reviewed a business and username's review next to them. 

For example:

 

 Sunny Bowl (3 reviews)

 [ Mike ] wrote: Simple, fresh, healthy bi-bim-bap for a very reasonable price.

 

Vive Sol (4 reviews)

[ Dave ] wrote: Great green salsa, great margaritas and such yummy food.

 

The Sandwich Spot (1 review)

[ Liza ] wrote:  Dutch crunch bread is freshly baked, not too doughy.

 

But unfortunately with my query I get 

 

 

 Sunny Bowl (3 reviews)

 [ Mike ] wrote: Simple, fresh, healthy bi-bim-bap for a very reasonable price.

 

 Sunny Bowl (3 reviews)

 [ Steve ] wrote: blah blah blah.

 

Vive Sol (2 reviews)

[ Dave ] wrote: Great green salsa, great margaritas and such yummy food.

 

 Sunny Bowl (3 reviews)

 [ Jose ] wrote: blah blah blah.

 

Vive Sol (2 reviews)

[ Jon ] wrote: blah blah blah.

 

The Sandwich Spot (1 review)

[ Liza ] wrote:  Dutch crunch bread is freshly baked, not too doughy.

 

So, as you see the query fetches everything, and it looks ugly. If 10 users review Sunny Bowl, then my homepage becomes a Sunny Bowl page, I don't want that.

 

Here is my query: 

 

 

	public function get_city_reviews($city_id,$limit,$offset)
	{
		$list = array();
               // $this->db->distinct('bizid');
		$this->db->from('review');
		$this->db->join('biz','biz.id = review.bizid');
		$this->db->where('biz.city_id',$city_id);
                
                
		$this->db->order_by('review.created_at','desc');
		$this->db->limit($limit,$offset);
                //$this->db->group_by('bizid');
		$query = $this->db->get();
		foreach($query->result() as $row)
		{
			$list[] = $row;
		}
		return $list;
	}

On the model side I have:

$data['reviews'] = $this->reviews->get_city_reviews($city->id,10,0);

 When I use GROUB BY it get exactly what I am looking for but it returns a username who posted (added) the business and I the first review for that business.

 

Lets say Sunny Bowl added by Admin and it was first reviewed by Jose. My expectation is :

 

 

 Sunny Bowl (3 reviews)

 [ Jose ] wrote: blah blah blah.

 

But instead I getting:

 

 

 Sunny Bowl (3 reviews)

 [ Admin ] wrote: blah blah blah.

 

I hope someone will give me a clue, 

 

Thanks

Edited February 27, 2014 by rocks