Question 368728 in Laravel Question posted in
The official Laravel Documentation can be found here.

Laravel – Select first model with condition OR select random model with no conditions

I’m not sure how I should have titled this question. I have a table called products. A simplified schema is:

+--------+-----------+----------+
|   id   | name      | priority |
+--------+-----------+----------+
|    1   | Product 1 |  null    | 
|    2   | Product 2 |  1       | 
|    3   | Product 3 |  null    | 
|    4   | Product 4 |  null    | 
|    5   | Product 5 |  null    |

Every time a user clicks a button, I aim to retrieve a single, random product. However, my preference is to return any product with a priority before I begin returning random products. (I have additional logic in place to prevent the same product from being selected more than once.)

An example is:

$product = Product::where('priority', '>', 0)->sortBy('priority')->first();

if(!$product) {
  $product = Product::OrderByRaw('Rand()')->first();
}

This works just fine, but it seems odd that I always have to run two queries to get one result.

So, my question is, is there a single query that I can use to achieve the same thing? I know I could do just one DB query to get all results and then get a product at random from the result set, but in my real-word application, the query is triggered as part of a much bigger AJAX call. Doing that would require a lot of rewriting of perfectly functioning code.

Tags:

2

Answers


  1. 0

    You can use the union method to combine two queries. The union method will execute the first query; if it doesn’t return any results, it will execute the second query.

    $priorityProduct = Product::where('priority', '>', 0)->orderBy('priority');
$randomProduct = Product::orderByRaw('RAND()');

$product = $priorityProduct->union($randomProduct)->first();
    Login or Signup to reply.
  2. 0

    Yes, you can potentially get the data you want with one query.

    However, having your query split up as you do may be slightly more efficient, depending on the size of your dataset. An order by rand() statement is not very efficient, especially on larger datasets. By using two statements as you have done, you are completely avoiding that second inefficient query when a result is found by the first efficient one.

    That said, below is one method for doing it in one query. This answer also assumes MySQL, where nulls are sorted higher than non-nulls (which is not what you want in this case).

    You can use one query, order it in the proper way, and then take the first result. In your case, it seems like you want your items ordered:

    1. all non-null priorities come before any null priority
    2. all non-null priorities in ascending order
    3. all null priorities in random order

    This can be accomplished with the following order by clause:

    ORDER BY
    priority is null,
    priority,
    rand()
    • First, priority is null will return 1 (true) when null and 0 (false) when not null. Since this sort is in ascending order, this sorts all non-null priorities higher than null priorities.
    • Next, the priority field will sort all non-null priorities in ascending order.
    • Finally, the rand() sort will sort all of the null priorities in random order.

    With all your records sorted in that order, take the first result.

    If you have a non-null priority, it will be first (ordered by priority asc). If you do not have a non-null priority, the first record will be a null priority that was sorted in random order.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search