如何用学说随机选择

2022-01-03 00:00:00 php doctrine-orm dql

这是我查询数据库中一些单词的方法

Here is how I query my database for some words

$query = $qb->select('w')
    ->from('DbEntitiesEntityWord', 'w')
    ->where('w.indictionary = 0 AND w.frequency > 3')
    ->orderBy('w.frequency', 'DESC')
    ->getQuery()
    ->setMaxResults(100);

我正在使用 mysql，我想获得符合条件的随机行，我将在查询中使用 rand() 排序.

I'm using mysql and I'd like to get random rows that match the criteria, I would use order by rand() in my query.

我发现了这个类似的问题，这基本上表明，因为 ORDER BY RAND 不受支持在学说中，您可以改为随机化主键.但是，在我的情况下无法做到这一点，因为我有一个搜索条件和一个 where 子句，因此并非每个主键都满足该条件.

I found this similar question which basically suggests since ORDER BY RAND is not supported in doctrine, you can randomize the primary key instead. However, this can't be done in my case because I have a search criteria and a where clause so that not every primary key will satisfy that condition.

我还发现了一个 代码片段，建议您使用 OFFSET 来随机化行像这样:

I also found a code snippet that suggests you use the OFFSET to randomize the rows like this:

$userCount = Doctrine::getTable('User')
     ->createQuery()
     ->select('count(*)')
     ->fetchOne(array(), Doctrine::HYDRATE_NONE); 
$user = Doctrine::getTable('User')
     ->createQuery()
     ->limit(1)
     ->offset(rand(0, $userCount[0] - 1))
     ->fetchOne();

我有点困惑，这是否会帮助我解决在我的情况下不支持随机排序的问题.我无法在 setMaxResult 之后添加偏移量.

I'm a little confused as to whether this will help me work around the lack of support for order by random in my case or not. I was not able to add offset after setMaxResult.

知道如何做到这一点吗?

Any idea how this can be accomplished?

推荐答案

Doctrine 团队 不愿意实现此功能.

The Doctrine team is not willing to implement this feature.

有多种解决方案可以解决您的问题，每种方法都有自己的缺点:

There are several solutions to your problem, each having its own drawbacks:

  • 添加自定义数字函数:请参阅此DQL RAND() 函数
    (如果您有很多匹配的行，可能会很慢)
  • 使用本机查询
    (我个人尽量避免使用这个解决方案，我发现它很难维护)
  • 首先发出原始 SQL 查询以随机获取一些 ID，然后通过将 ID 数组作为参数传递，使用 DQL WHERE x.id IN(?) 加载关联的对象.
    此解决方案涉及两个单独的查询，但可能比第一个解决方案提供更好的性能(存在除 ORDER BY RAND() 之外的其他原始 SQL 技术，我不会在此详细说明)，你会在这个网站上找到一些很好的资源).
  • Add a custom numeric function: see this DQL RAND() function
    (might be slow if you have lots of matching rows)
  • Use a native query
    (I personally try to avoid this solution, which I found hard to maintain)
  • Issue a raw SQL query first to get some IDs randomly, then use the DQL WHERE x.id IN(?) to load the associated objects, by passing the array of IDs as a parameter.
    This solution involves two separate queries, but might give better performance than the first solution (other raw SQL techniques than ORDER BY RAND() exist, I won't detail them here, you'll find some good resources on this website).

相关文章