0

I have models Book and BookCategory

How do I select the cheapest book in every category?

Book table: 

| id | name | price | book_category_id |  
| 1 | test | 10 | 1  
| 2 | test | 15 | 3  
| 3 | test | 75 | 1  
| 4 | test | 25 | 2  
| 5 | test | 19 | 1  
| 6 | test | 11 | 2  
| 7 | test | 10 | 1

The selection should be : 

| id | name | price | book_category_id |  
| 1 | test | 10 | 1  
| 2 | test | 15 | 3  
| 6 | test | 11 | 2

I've tried: 

$books = Book::groupBy("book_category_id")->orderBy("price")->get()

But the output is not the minimum price row.

any idea?

EDIT:

I found this page: https://www.xaprb.com/blog/2006/12/07/how-to-select-the-firstleastmax-row-per-group-in-sql/

it has 90% of the solution in SQL 

    SELECT *
    FROM books
    WHERE price = (SELECT MIN(price) FROM books AS u WHERE u.book_category_id= books.book_category_id)
GROUP BY books.book_category_id

how to convert this to laravel query builder?

Zorox
  • 2,040
  • 2
  • 21
  • 28

1 Answers1

0

You need to perform a subquery like this post. Try this:

$books = Book::from(DB::raw("SELECT * FROM books order by price asc"))
             ->groupBy("book_category_id")->get();

Please note that this is a mysql only solution because in mysql you're allowed to not aggregate non-group-by columns. If you need to do this for another DB, you need to perform an inner join on the subquery

Community
  • 1
  • 1
Paras
  • 9,258
  • 31
  • 55
  • this return exactly like my code. it return the first record of every group + extra filed min_price. (the min_price, is not necessary the price of the returned record – Zorox Mar 01 '17 at 20:01
  • still , even with rearranging, the result is not the minimum record – Zorox Mar 01 '17 at 20:36
  • What is the result? Did you use the exact code? And is your DB mysql? – Paras Mar 01 '17 at 20:37
  • Oops, there was a typo, try the updated code. Note the change from `desc` to `asc` – Paras Mar 01 '17 at 20:38
  • Okay, what is the result in that case and are you using mysql? – Paras Mar 01 '17 at 20:41
  • i updated the question with something that may help, check that out please – Zorox Mar 01 '17 at 20:48
  • Dont think the query you posted is the most optimal. I would say this query would be the best: `select * from (select * from books order by price) group by book_category_id`. You can try it in mysql. If you post the output of my answer, I can try to help – Paras Mar 01 '17 at 20:53
  • relying on "group by" and order is not reliable, you cannot control which row "group by" select – Zorox Mar 02 '17 at 07:59
  • This query is guaranteed to give the min price. The query you have mentioned will also give min price row. Both queries don't guarantee which row to return if 2 rows have same price – Paras Mar 02 '17 at 08:14