Eloquent 3 table query

how do u define most used? is there another table or something? also if you put your raw SQL it would help people better understand what u want.

Here's raw query

SELECT c.name, COUNT(r.category_id) as most_used FROM users u JOIN recipes r ON u.id = r.user_id JOIN categories c ON r.category_id = c.id GROUP BY c.name ORDER BY most_used DESC LIMIT 3

Which returns: Cake Recipess 4 cookies 2 Smoothie Recipes 1

auth()->user() will get the currently logged-in user, you can work through the relationships from there:

You should set up the inverse relationship for the category model.

Category.php

public function recipes() 
{
    return this->hasMany(Recipes::class);
}

To do the query you can chain the relationships as such - using the with facade to include a recipes' category.

$user_id = auth()->user()->id;

$results = User::with('recipes.category')->where('id', $user_id)->get()

You could then do a groupBy() and then do a count.

$results->select('id', \DB::raw("count(id)"))->groupBy('category_id')->get();

You could do it in one go, of course, but I tried to highlight each step.

BadMethodCallException Method Illuminate\Database\Eloquent\Collection::with does not exist.

when i try $user->recipes->with..

@jredli93 , apologies, with is a facade, I updated my response.

Please note, ->get() returns a collection from a query builder object, so you would not do that twice for a single query like I have.

Further more, you can use php artisan tinker from the command line to play with the query and tweak it.

Still can't manage to make it work :/