如何随机选择学说 [英] How to select randomly with doctrine

问题描述

这是我如何查询我的数据库的一些单词

Here is how I query my database for some words

$query = $qb->select('w')
    ->from('DbEntities\Entity\Word', '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.

任何想法如何实现？

推荐答案

教条小组不愿意实施此功能

您的问题有几种解决方案，每种都有自己的缺点：

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

• 添加自定义数字函数：请参阅此 DQL RAND（）函数
  
  （如果您有很多匹配的行可能会很慢）


• 使用本机查询
  
  （我个人试图避免这个解决方案，我觉得很难维护）
  
• 首先发出原始SQL查询以随机获取一些ID，然后使用DQL WHERE x.id IN（？）加载关联的通过传递ID数组作为参数。
  
  此解决方案涉及两个单独的查询，但是可能比第一个解决方案（其他原始SQL技术比 ORDER BY RAND（）存在，我不会在这里详细说明，你会在这个网站上找到一些很好的资源。）

• 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).

这篇关于如何随机选择学说的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！