Caleb Brewer Tue Oct 08 00:05:35 -0400 2013

Subject: Ratings between classes

I am working with two classes: users and categories, which can be associated with another class: category_ratings. I would like to retrieve all categories with their ratings, which in raw SQL would look something like this:

SELECT c.*, cr.rating FROM categories c JOIN category_ratings cr ON c.id = cr.category_id WHERE cr.user_id = ?

In trying to implement this with phpactiverecord I have done the following:

class User extends ActiveRecord\Model {
static $has_many = array(
array('category_ratings')
);
}
class CategoryRating extends ActiveRecord\Model {

static $belongs_to = array(
array('user'),
array('category')
);
function view() {
$return = $this->category->view();
$return['rating'] = $this->rating;
return $return;
}
}
class Category extends ActiveRecord\Model {
static $has_many = array(
array('category_ratings')
);
function view() {
return array(
'term' => $this->name,
'id' => $this->id,
'rating' => '0'
);
}
}

The issue with my phpactiverecord implementation is that only categories that have been rated by the user will be returned, which I can do using this:
$user = User::find_by_id($id, array('include'=>array('category_ratings'=>array('category'))));
The problem is that this only returns categories that the user has rated by I want all categories.
I know I can also do the relationship by having categories related to users 'through' category_ratings but when retrieving this way I am not able to access the 'rating' column on the category rating. Any thoughts? Thanks!


Caleb Brewer Tue Oct 08 22:22:25 -0400 2013

I was able to solve my issue by accessing the categories from the categories model instead of the user model. This effectively replaces the use of my associations for this case so it probably is not best practice but it works none the less!

$categories = Category::find_by_sql(<<<EOD
select c.*, cr.rating from categories c
left outer join (
select category_id,rating from category_ratings
where user_id = ?
) cr on c.id = cr.category_id
EOD
,array($session->user->id));

By selecting the rating field from the joined table, the category object returned now has an accessible rating field! This field is most likely read only because it is not an actual category_rating object so I am still looking for the best practice solution if anyone knows it, but this works perfectly for read only displaying which is all I need.

(1-1/1)