Jump to content

Retrieve last user reviewed a post


rocks

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

Edited by rocks
Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • 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.