php 如何用学说随机选择
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/10762538/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to select randomly with doctrine
提问by Yasser1984
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);
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.
我正在使用 mysql，我想获得符合条件的随机行，我将在查询中使用 rand() 排序。
I found this similarquestion 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.
我发现了这个类似的问题，它基本上表明，因为 ORDER BY RAND 在学说中不受支持，您可以改为随机化主键。但是，这在我的情况下无法完成，因为我有一个搜索条件和一个 where 子句，因此并非每个主键都满足该条件。
I also found a code snippetthat suggests you use the OFFSET to randomize the rows like this:
我还发现了一个代码片段，建议您使用 OFFSET 来随机化这样的行：
$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();
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.
我有点困惑，这是否会帮助我解决在我的情况下不支持随机排序的问题。我无法在 setMaxResult 之后添加偏移量。
Any idea how this can be accomplished?
知道如何做到这一点吗？
回答by Benjamin
The Doctrine team is not willing to implement this feature.
Doctrine 团队不愿意实现这个特性。
There are several solutions to your problem, each having its own drawbacks:
您的问题有多种解决方案，每种都有自己的缺点：
- 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 mightgive better performance than the first solution (other raw SQL techniques thanORDER BY RAND()exist, I won't detail them here, you'll find some good resources on this website).
- 添加自定义数字函数：请参阅此DQL RAND() 函数
（如果您有很多匹配的行，可能会很慢） - 使用本机查询
（我个人尝试避免使用此解决方案，我发现它很难维护） - 首先发出原始 SQL 查询以随机获取一些 ID，然后
WHERE x.id IN(?)通过将 ID 数组作为参数传递，使用 DQL加载关联对象。
这个解决方案涉及两个单独的查询，但可能比第一个解决方案提供更好的性能（其他原始 SQL 技术不ORDER BY RAND()存在，我不会在这里详细介绍，你会在这个网站上找到一些很好的资源）。
回答by HMagdy
Follow these steps:
按着这些次序：
Define a new class at your project as:
在您的项目中定义一个新类：
namespace My\Custom\Doctrine2\Function;
use Doctrine\ORM\Query\Lexer;
class Rand extends \Doctrine\ORM\Query\AST\Functions\FunctionNode
{
public function parse(\Doctrine\ORM\Query\Parser $parser)
{
$parser->match(Lexer::T_IDENTIFIER);
$parser->match(Lexer::T_OPEN_PARENTHESIS);
$parser->match(Lexer::T_CLOSE_PARENTHESIS);
}
public function getSql(\Doctrine\ORM\Query\SqlWalker $sqlWalker)
{
return 'RAND()';
}
}
Register the class config.yml:
注册课程 config.yml：
doctrine:
orm:
dql:
numeric_functions:
Rand: My\Custom\Doctrine2\Function\Rand
Use it directly as:
直接使用它作为：
$qb->addSelect('RAND() as HIDDEN rand')->orderBy('rand()'); //Missing curly brackets
回答by Jonny
In line with what Hassan Magdy Saad suggested, you can use the popular DoctrineExtensionslibrary:
根据 Hassan Magdy Saad 的建议，您可以使用流行的DoctrineExtensions库：
See mysql implementation here: https://github.com/beberlei/DoctrineExtensions/blob/master/src/Query/Mysql/Rand.php
在此处查看 mysql 实现：https: //github.com/beberlei/DoctrineExtensions/blob/master/src/Query/Mysql/Rand.php
# config.yml
doctrine:
orm:
dql:
numeric_functions:
rand: DoctrineExtensions\Query\Mysql\Rand
Tested in Doctrine ORM 2.6.x-dev, you can then actually do:
在 Doctrine ORM 2.6.x-dev 中进行测试，然后您可以实际执行以下操作：
->orderBy('RAND()')
回答by Derek
Or you could do this -->
或者你可以这样做-->
$words = $em->getRepository('Entity\Word')->findAll();
shuffle($words);
Of course this would be very inefficient if you have many records so use with caution.
当然，如果您有很多记录，这将非常低效，因此请谨慎使用。
回答by Krzysztof Trzos
Why not to use repository?
为什么不使用存储库？
<?php
namespace Project\ProductsBundle\Entity;
use Doctrine\ORM;
class ProductRepository extends ORM\EntityRepository
{
/**
* @param int $amount
* @return Product[]
*/
public function getRandomProducts($amount = 7)
{
return $this->getRandomProductsNativeQuery($amount)->getResult();
}
/**
* @param int $amount
* @return ORM\NativeQuery
*/
public function getRandomProductsNativeQuery($amount = 7)
{
# set entity name
$table = $this->getClassMetadata()
->getTableName();
# create rsm object
$rsm = new ORM\Query\ResultSetMapping();
$rsm->addEntityResult($this->getEntityName(), 'p');
$rsm->addFieldResult('p', 'id', 'id');
# make query
return $this->getEntityManager()->createNativeQuery("
SELECT p.id FROM {$table} p ORDER BY RAND() LIMIT 0, {$amount}
", $rsm);
}
}
回答by Andrés Moreno
For me, the most useful way was to create two arrays where i say order type and different properties of the Entity. For example:
对我来说，最有用的方法是创建两个数组，其中我说的是订单类型和实体的不同属性。例如：
$order = array_rand(array(
'DESC' => 'DESC',
'ASC' => 'ASC'
));
$column = array_rand(array(
'w.id' => 'w.id',
'w.date' => 'w.date',
'w.name' => 'w.name'
));
You could add more entries to array $column like criteria.
您可以像条件一样向数组 $column 添加更多条目。
Afterwards, you can build your query with Doctrine adding $column and $order inside ->orderBy. For example:
之后，您可以使用 Doctrine 在 ->orderBy 中添加 $column 和 $order 来构建查询。例如：
$query = $qb->select('w')
->from('DbEntities\Entity\Word', 'w')
->where('w.indictionary = 0 AND w.frequency > 3')
->orderBy($column, $order)
->getQuery()
->setMaxResults(100);
This way improved the performance of my application. I hope this helps someone.
这种方式提高了我的应用程序的性能。我希望这可以帮助别人。
回答by PayteR
@Krzysztof's solution is IMHO best here, but RAND() is very slow on large queries, so i updated @Krysztof's solution to gives less "random" results, but they are still random enough. Inspired by this answer https://stackoverflow.com/a/4329492/839434.
@Krzysztof 的解决方案在这里是最好的恕我直言，但是 RAND() 在大型查询上非常慢，所以我更新了 @Krysztof 的解决方案以减少“随机”结果，但它们仍然足够随机。灵感来自这个答案https://stackoverflow.com/a/4329492/839434。
namespace Project\ProductsBundle\Entity;
use Doctrine\ORM;
class ProductRepository extends ORM\EntityRepository
{
/**
* @param int $amount
* @return Product[]
*/
public function getRandomProducts($amount = 7)
{
return $this->getRandomProductsNativeQuery($amount)->getResult();
}
/**
* @param int $amount
* @return ORM\NativeQuery
*/
public function getRandomProductsNativeQuery($amount = 7)
{
# set entity name
$table = $this->getClassMetadata()
->getTableName();
# create rsm object
$rsm = new ORM\Query\ResultSetMapping();
$rsm->addEntityResult($this->getEntityName(), 'p');
$rsm->addFieldResult('p', 'id', 'id');
# sql query
$sql = "
SELECT * FROM {$table}
WHERE id >= FLOOR(1 + RAND()*(
SELECT MAX(id) FROM {$table})
)
LIMIT ?
";
# make query
return $this->getEntityManager()
->createNativeQuery($sql, $rsm)
->setParameter(1, $amount);
}
}
回答by araldh
Shuffling can be done on the query (array) result, but shuffling does not pick randomly.
可以对查询（数组）结果进行混洗，但混洗不会随机选择。
In order to pick randomly from an entity I prefer to do this in PHP, which might slow the random picking, but it allows me to keep control of testing what I am doing and makes eventual debugging easier.
为了从实体中随机选择，我更喜欢在 PHP 中执行此操作，这可能会减慢随机选择的速度，但它允许我保持对正在执行的测试的控制并使最终调试更容易。
The example below puts all IDs from the entity into an array, which I can then use to "random-treat" in php.
下面的示例将实体中的所有 ID 放入一个数组中，然后我可以使用它在 php 中“随机处理”。
public function getRandomArt($nbSlotsOnPage)
{
$qbList=$this->createQueryBuilder('a');
// get all the relevant id's from the entity
$qbList ->select('a.id')
->where('a.publicate=true')
;
// $list is not a simple list of values, but an nested associative array
$list=$qbList->getQuery()->getScalarResult();
// get rid of the nested array from ScalarResult
$rawlist=array();
foreach ($list as $keyword=>$value)
{
// entity id's have to figure as keyword as array_rand() will pick only keywords - not values
$id=$value['id'];
$rawlist[$id]=null;
}
$total=min($nbSlotsOnPage,count($rawlist));
// pick only a few (i.e.$total)
$keylist=array_rand($rawlist,$total);
$qb=$this->createQueryBuilder('aw');
foreach ($keylist as $keyword=>$value)
{
$qb ->setParameter('keyword'.$keyword,$value)
->orWhere('aw.id = :keyword'.$keyword)
;
}
$result=$qb->getQuery()->getResult();
// if mixing the results is also required (could also be done by orderby rand();
shuffle($result);
return $result;
}
回答by Safwan Bakais
I hope this would help others:
我希望这会帮助其他人：
$limit = $editForm->get('numberOfQuestions')->getData();
$sql = "Select * from question order by RAND() limit $limit";
$statement = $em->getConnection()->prepare($sql);
$statement->execute();
$questions = $statement->fetchAll();
Note here the table question is an AppBundle:Question Entity. Change the details accordingly. The number of questions is taken from the edit form, make sure to check the variable for the form builder and use accordingly.
注意这里的表格问题是一个 AppBundle:Question 实体。相应地更改详细信息。问题数量取自编辑表单，确保检查表单构建器的变量并相应地使用。
回答by kyeno
Probably the easiest (but not necessarily the smartest) way to get a single objectresult ASAP would be implementing this in your Repository class:
尽快获得单个对象结果的最简单（但不一定是最聪明）的方法可能是在您的 Repository 类中实现它：
public function findOneRandom()
{
$className = $this->getClassMetadata()->getName();
$counter = (int) $this->getEntityManager()->createQuery("SELECT COUNT(c) FROM {$className} c")->getSingleScalarResult();
return $this->getEntityManager()
->createQuery("SELECT ent FROM {$className} ent ORDER BY ent.id ASC")
->setMaxResults(1)
->setFirstResult(mt_rand(0, $counter - 1))
->getSingleResult()
;
}
