Laravel-在应用``order by''后在特定列中随机选择n个包含相同值的行 [英] Laravel - randomly select n number of rows containing same value in certain column after applying 'order by'

问题描述

在我的Laravel项目中，在数据库表ads中，我具有以下结构:

In my Laravel project, in the database table ads, I have the following structure :

id | col1 | col2

col2具有类似topad，bump，urgent的值以及空值.我想从ads表中获取所有行，并根据col2降序对它们进行字母排序.

col2 has values like topad, bump,urgent along with empty value. I want to take all the rows from the ads table and sort them alphabetically based on col2 in descending order.

所以我用过:

Ads::orderBy('col2','DESC')->get()

现在我有2个条件要应用于查询.

Now I have 2 conditions to be applied on the query.

第一个条件:假设在col2中有4行topad，在col2中有5行具有urgent，在col2中有6行具有bump和7在col2中每个具有空值的行.因此，在col2中带有urgent的行将显示为第1行，在col2中具有topad的行将显示为第2个，而在col2中具有bump的行将显示为第3个，而在col2中的空值将显示为第4个.现在，我需要在每个集合中随机化行的顺序.例如，在col2中带有topad的行可能具有ids 1、2、3、4.我想随机化这些行(可能会导致例如4,2,1,3).但是它们将出现在col2中包含topad的行之前.对于topad和bump行集以及包含col2中任何空值的行，都是如此.

1st condition : Suppose there are 4 rows with topad in col2, 5 rows with urgent in col2, 6 rows with bump in col2 and 7 rows each with an empty value in col2 . So rows with urgent in col2 will appear 1st, with topad in col2 will appear 2nd and with bump in col2 will appear 3rd and with empty values in col2 will appear 4th. Now I need to randomize the rows' order within each set. For example , rows with topad in col2 may have the ids 1,2,3,4. I want to randomize these rows (which may result into for example 4,2,1,3). But they will appear before rows containing topad in col2. Same is true for topad and bump row sets and rows containing any empty value in col2.

因此查询变为:

Ads::orderBy('col2','DESC')->inRandomOrder()->get(); 

第二个条件:假设行按col2值排序.但是，对于每组在col2中包含相同值的行，我需要n从那些在col2中具有非空值的行，即 randomly urgent行，n来自topad ed行，n来自bump ed行， all 来自empty ed行.

2nd condition : Suppose rows are ordered by col2 values. But from each set of rows containing same value in col2, I need n number of rows from those that have non-empty value in col2 i.e. randomly I need n rows from urgented rows, n from topaded rows, n from bumped rows and all from emptyed rows.

然后如何编写查询?

推荐答案

您可以使用子查询来执行此操作，但是根据我的经验，它们需要花费更多的时间来执行，然后执行一些较小的操作(如果索引正确).另外，您可以更好地控制限制和调试问题.

You could do this with subqueries, but in my experience they take more time to execute then a few smaller ones (if they are indexed correctly). Also, you have more control over the limits and debugging issues.

$top_ads = Ads::whereCol2('topad')->inRandomOrder()->limit(5)->get();
$urgent_ads = Ads::whereCol2('urgent')->inRandomOrder()->limit(10)->get();
$bump_ads = Ads::whereCol2('bump')->inRandomOrder()->limit(2)->get();

这将创建您的查询，然后您可以对它们的集合进行任何操作.合并它们，对其重新排序，等等.

This will create your queries and after that you can do whatever you want with their collections. Combine them, reorder them, etc.

这篇关于Laravel-在应用``order by''后在特定列中随机选择n个包含相同值的行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！