Nova Filter Query - Translate from SQL

I've a Model with some ratings. With the Nova filter, I want only to show the results with a selected rating ($value = 1|2|3|4|5| >= 3| ...).

This SQL is working fine:

SELECT p.*, AVG(pr.rating) as rating
                FROM `posts` p
                JOIN post_ratings pr ON pr.post_id = p.id
                GROUP BY p.id, p.brand, p.company, p.url, p.is_public, p.created_at, p.updated_at, p.deleted_at
                HAVING ROUND(rating, 0) = $value

now i tried to "translate" that query for the apply method:

public function apply(Request $request, $query, $value)
    {
return $query->select([
               'posts.id',
               \DB::raw('AVG(`post_ratings`.`rating`) as rating'),
            ])
                ->join('post_ratings', 'post_ratings.post_id', '=', 'posts.id')
                ->groupBy('post.id')
                ->havingRaw('ROUND(`rating`, 0) = ?', [$value]);
}

here I'm getting an error message: unknown column rating in having clause

I'm feeling blind... I'm not finding the error here..?

jlrdw

7 years ago

Best Answer

Level 75

This SQL is working fine:

Just use the working sql.

https://laracasts.com/discuss/channels/laravel/sql-native-to-query-builder

Have you tried to place the join first, I had to reverse one, just example of the join first:

$quy = DB::connection('mysqlv2')->table('dc_powners')
                ->leftJoin('dc_pets', 'dc_powners.ownerid', '=', 'dc_pets.ownerid')
                ->select('dc_powners.ownerid', 'dc_powners.oname')->distinct()
                ->selectRaw('count(dc_pets.petid) as CountOfpetid')
                ->where('dc_powners.ownerid', '<', 5)  // where for testing
                ->groupby('dc_powners.ownerid')
                ->orderby('dc_powners.oname')
                ->get();

Remember these queries can take a little trial and error to figure out.

1 like

Please or to participate in this conversation.