dumbo/app/models/Film.php

<?php

class Film extends Eloquent {
	protected $table = "films";

	public function comments() {
		return $this->hasMany('Comment', 'film');
	}

	public function votes() {
		return $this->hasMany('Vote', 'film');
	}

	public function besitzer() {
		return $this->belongsTo('User', 'user');
	}

	public function scopeZuletztGesehen($query) {
		return $query->whereNull('abgelehnt')->whereNotNull('gesehen')->orderBy('gesehen', 'DESC')->take(5);
	}

	public function scopeZuletztAbgelehnt($query) {
		return $query->whereNull('gesehen')->whereNotNull('abgelehnt')->orderBy('abgelehnt', 'DESC')->take(5);
	}

	public function scopeNeuesteVorschlage($query) {
		return $query->whereNull('gesehen')->whereNull('abgelehnt')->orderBy('updated_at', 'DESC')->take(5);
	}

    public function scopeMeistgewunschteVorschlage($query) {
        $nextfilm = Dumbo::find(1)->film;
        return $query
            ->addSelect(DB::raw('film_films.*, COUNT(case when film_votes.stimme IS TRUE then 1 end) as upvotes, COUNT(*) as votes'))
            ->leftJoin('votes', 'votes.film', '=', 'films.id')
            ->leftJoin('users', 'users.id', '=', 'votes.user')
            ->whereNull('gesehen')
            ->whereNull('abgelehnt')
            ->where('films.id', "!=", is_object($nextfilm) ? $nextfilm->id : 0)
            ->where('users.settings', 'NOT LIKE', '%disabled":true%')
            ->groupBy('films.id')
            ->orderBy('upvotes', 'DESC')
            ->orderBy('vorgeschlagen', 'ASC');
    }

	public function getVotes() {
        return Vote::where('film', '=', $this->id)
                                             ->where('users.settings', 'NOT LIKE', '%disabled":true%')
                                             ->leftJoin('users', 'users.id', '=', 'votes.user')
                                             ->count();
    }

    public function getUpvotes() {
        return Vote::where('film', '=', $this->id)
                                             ->whereRaw('stimme IS TRUE')
                                             ->where('users.settings', 'NOT LIKE', '%disabled":true%')
                                             ->leftJoin('users', 'users.id', '=', 'votes.user')
                                             ->count();
    }

    public function getBewertung() {
        if(!is_null($this->gesehen)) {
            $count = Comment::where('film', $this->id)->where('bewertung', '>', 0)->count();
            return $count > 0 ? Comment::where('film', $this->id)->where('bewertung', '>', 0)->sum('bewertung') / $count : 0;

        } else return (0);

    }

    public function getPoster() {
        $tmdb = new TMDb(Config::get('constants.tvdb.apikey'), 'de', TRUE);
        $tmovie = $tmdb->getMovie($this->tvdbid);
        return $tmdb->getImageUrl($tmovie['poster_path'], TMDb::IMAGE_POSTER, 'w342');
    }

}