2

I have three tables a product, user and purchases table. I am trying to return a list of the category of products that the user has most frequently purchased. So the list should look like : Garden 4, Food 3, Sport 2 etc.

Here are my tables:

products: id, name, categories_id, price categories: id, category users: id, name
purchases: id, users_id, products_id

I am struggling to work out how to do this, this is what i have so far

Purchase::with('products')->where('purchases.users_id', '=', Auth::users->id())
detinu20
  • 299
  • 3
  • 20

2 Answers2

1

explanation:

first we select all products purchased by the current user, than we join it with the products table, we group our rows with the category attribute, and in the we select category, COUNT(*).

implementation : 

Purchase::where("purchases.user_id","=",Auth::user()->id)
          ->join("products","products.id","=","purchases.product_id")
          ->join("categories","products.categories_id","=","categories.id")
          ->select('categories.category', DB::raw('count(*) as total'))
          ->groupBy('products.category')
          ->get()
Djellal Mohamed Aniss
  • 1,723
  • 11
  • 24
  • This is good but i have just realised that it is a categories_id field and the category list comes from a category table, how would this work and how would i return the category name and total through variables? – detinu20 Feb 21 '19 at 18:11
0

Check if this is what you need : 

$categories = Category::join('products', 'products.category', '=', 'categories.id')
->join('purchases','purchases.products_id', '=','products.id')
->select('categories.*')
->groupBy('purchases.product_id', 'categories.id')
->orderBy(\DB::raw('count(*) as product_purchase_count'), 'desc')
->get();
Mihir Bhende
  • 8,677
  • 1
  • 30
  • 37