0

I have library-like system. In one table bunch of books in different copies and other table for reservations.

Books table (for simplisity only three columns)

id title inventory_number
1 Coding is awesome 1111
2 Coding is awesome 2222
3 Coding is awesome 3333
4 Get rich quick 4444
5 Get rich quick 5555
6 YOLO 6666

Reservation table (for simplisity only three columns)

id book_id user_id
1 1 1
2 1 2
3 2 1
4 4 1
5 5 2

Although in Books table is 6 books, to end user it means that there is only 3 books - "Coding is awesome", "Get rich quick" and "YOLO" and he don't need to know nothing about other book copies in stock.

When user comes to "Library/Store" and asks for some book than he should get the first book with minimum reservations for current book.

From given data above can be understood that:

  • "Coding is awesome" (ID:1) is reserved 2 times
  • "Coding is awesome" (ID:2) is reserved 1 times
  • "Coding is awesome" (ID:3) is reserved 0 times

So when user asks for "Coding is awesome" book he needs to get book with ID:3 And when he asks for "Get ritch quick" he gets one of two books because all two books is reserved equal number of times.

I need a solution for "end user" that will return only books with different (unique) title and other to current book related data

Although in Books table is 6 books, In this case need to return three books:

id title inventory_number
3 Coding is awesome 3333
4 Get rich quick 4444
6 YOLO 6666

If there will be more books with diferent names then they also need to be shown. (for each unique title one record)

If it is possible then I need to return these data from database using Eloquant or Query Builder not filter out with Collections to save resources and use pagging.

Using Laravel 8, Mysql 5.7

sidnas
  • 425
  • 5
  • 12
  • It's not just the min value need (that's a relatively simple query), but you need min of counts, which is an aggregation of aggregation. What exactly do you mean by getting the answer with eloquent or query builder? Do you mean raw sql statement? – Shadow Mar 14 '22 at 12:42
  • With eloquent or query builder I mean something like Books::query()... or DB::table('books'). But that is only for "nicer code look". If it's not or hard to achive than raw sql also can be solution – sidnas Mar 14 '22 at 13:25

1 Answers1

-1

Assuming you have a reservations relationship set up in the Book model, using withCount, you can get a reservations_count for all books.

Book::query()
    ->select('id', 'title', 'inventory_number')
    ->withCount('reservations')
    ->get();

This will give you a result set like

id title inventory_number reservations_count
1 Coding is awesome 1111 2
2 Coding is awesome 2222 1
3 Coding is awesome 3333 0
4 Get rich quick 4444 0
5 Get rich quick 5555 0
6 YOLO 6666 0

Let's call this query 1. You could make something like

SELECT *
FROM (
    -- query 1
) books
GROUP BY id, title, inventory_number
ORDER BY MAX(reservations_count) DESC

to get only one relevant row.

In theory, this should work.

$query_1 = Book::query()
    ->select('id', 'title', 'inventory_number')
    ->withCount('reservations');

$results = Book::query()
    ->fromSub($query_1, 'books')
    ->groupByRaw('id, title, inventory_number')
    ->orderByRaw('reservations_count ASC')
    ->limit(3)
    ->get();

I found something similar at this other question

Another possibility is to use Collections to do the hard part.

$results = Book::query()
    ->withCount('reservations')
    ->get() // replace with cursor() if you're not using `with()` in the query
    ->sortBy('reservations_count')
    ->unique('title')
    ->take(3); // or ->random(3)
IGP
  • 14,160
  • 4
  • 26
  • 43
  • Thanks for fast respons, but using $query_1 and subquery for $result is half of result what I need. It still return all books, but I olny need 3 (with unique titles). And if I undesrtand correctly than with ID column in group by it's not possible to achive. – sidnas Mar 14 '22 at 13:20
  • You should be able to use a `limit(3)` on the `$results` query for that, or (using collections), use `take(3)` or `random(3)` instead of `values()`. – IGP Mar 14 '22 at 13:28
  • I guess I did not fully explain. limit(3) will not help. It's not like I need only three books. I need all available books if there is more than 3. In this example 3 is the unique book count. And by unuque book I mean books with same title. – sidnas Mar 14 '22 at 13:35
  • It's like in library or shop when you ask for specific book by name than employee brings you one of 10 or 100 available books and you don't know how many of those books actualy are there – sidnas Mar 14 '22 at 13:38
  • Yeah, this is something completely aside from what you asked and it's not really obvious to me. You should go into a lot more detail on your question by editing it. – IGP Mar 14 '22 at 13:44
  • Okay, I edited description, hopefully it will help better understand question and my problem – sidnas Mar 14 '22 at 14:04