Jump to content

Archived

This topic is now archived and is closed to further replies.

rocks

Retrieve last user reviewed a post

Recommended Posts

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

Share this post


Link to post
Share on other sites

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.