It would be incredibly useful to have the ability to do EFQueries with random ordering. This would allow the user to, eg. select a random node.

Comments

Dave Reid’s picture

Dave Reid
he/him
Primary language English
Location Nebraska USA
CreditAttribution: Dave Reid commented
Title: Order By Rand() in EFQ » Add an orderRandom method to EntityFieldQuery

orderRandom is already a part of SelectQueryInterface, just need to extend it to EFQ, but I'm not sure it can be done in a cross-platform way like DBTNG.

naught101’s picture

naught101 CreditAttribution: naught101 commented

http://eric.lubow.org/2010/databases/mongodb/getting-a-random-record-fro...

Horrible. I was about to start looking at writing a patch for this, but bojanz reminded me that EFQ works with MongoDB as well, and that method looks hellish.

http://groups.google.com/group/mongodb-user/browse_thread/thread/aae871e... looks far more sensible , but I have no idea how expensive this would be.
http://groups.google.com/group/norm-mongodb/browse_thread/thread/625b2ef...
mentions that methods like these can be expensive on large data sets?

The reason I bring this up is due to #1167024: Add support for devel to generate relations, where we want to select random entities to create relations between, but this could equally be useful in things like the random image block (I'm probably the only one in the world who uses that anyway, but it's a good example).

chx’s picture

chx CreditAttribution: chx commented

That method on MongoDB is http://xkcd.com/221/ this.

rv0’s picture

rv0 CreditAttribution: rv0 commented

Bumped into this when writing a random header image block. Using EFQ because I have to reduce the set depending on field values.

Would be useful indeed.

chx’s picture

chx CreditAttribution: chx commented

This link from #2 http://eric.lubow.org/2010/databases/mongodb/getting-a-random-record-fro... does look doable so if you are interested please go ahead with a patch.

chx’s picture

chx CreditAttribution: chx commented
Title: Add an orderRandom method to EntityFieldQuery » Add a random to every entity
Component: field system » entity system
Category: feature » task

Actually this needs to be done because if we would store a random with every entity then even MySQL could gain a much much faster order by random simply by ordering on that. Win.

chx’s picture

chx CreditAttribution: chx commented
Title: Add a random to every entity » Add an orderRandom method to EntityFieldQuery

Actually if UUIDs get in then ordering on that , I would like to believe, is sufficiently random. However, as #1346204: [meta] Drupal 8 Entity API improvements does not even list an UUID for entities issue I am leaving this open.

agoradesign’s picture

agoradesign CreditAttribution: agoradesign commented
Issue summary: View changes

Could we please revitalize this issue? Just needed this functionality by myself and was surprised, how inconvenient it is to solve to get the random order - you'd either have to use a normal SelectQuery instead entity query, or - if you don't have too many concerned entities in your DB - query all without range restriction, do a random sort on the node ids, slice the array and then load the nodes/entities...

Version: 8.0.x-dev » 8.1.x-dev

Drupal 8.0.6 was released on April 6 and is the final bugfix release for the Drupal 8.0.x series. Drupal 8.0.x will not receive any further development aside from security fixes. Drupal 8.1.0-rc1 is now available and sites should prepare to update to 8.1.0.

Bug reports should be targeted against the 8.1.x-dev branch from now on, and new development or disruptive changes should be targeted against the 8.2.x-dev branch. For more information see the Drupal 8 minor version schedule and the Allowed changes during the Drupal 8 release cycle.

TheDucksLover’s picture

TheDucksLover CreditAttribution: TheDucksLover commented

Yeah it would be great to have a solution out of the box.. As agoradesign said, atm I'm also getting all the entity IDs to finally use an array_rand while loading them, but well :/

Version: 8.1.x-dev » 8.2.x-dev

Drupal 8.1.9 was released on September 7 and is the final bugfix release for the Drupal 8.1.x series. Drupal 8.1.x will not receive any further development aside from security fixes. Drupal 8.2.0-rc1 is now available and sites should prepare to upgrade to 8.2.0.

Bug reports should be targeted against the 8.2.x-dev branch from now on, and new development or disruptive changes should be targeted against the 8.3.x-dev branch. For more information see the Drupal 8 minor version schedule and the Allowed changes during the Drupal 8 release cycle.

tomasnagy’s picture

tomasnagy CreditAttribution: tomasnagy as a volunteer commented

You can achieve this quite simple by adding a tag to your query.

First hook to create the tag.

/**
 * Implements hook_query_TAG_alter().
 */
function MODULE_query_random_alter(Drupal\Core\Database\Query\AlterableInterface $query) {
  $query->orderRandom();
}

Then just use add the tag to the required query.

$query->addTag('random');

Hope this helps

Chris Gillis’s picture

Chris Gillis CreditAttribution: Chris Gillis commented

@tomasnagy, does this add the random order after any existing order by statements? E.g. I want to order by foo, but for rows where foo is equal I want a random sort....

tomasnagy’s picture

tomasnagy CreditAttribution: tomasnagy as a volunteer commented

@Chris this basically adds 'ORDER BY RAND()' at the end of the query from what I've found in core.

Version: 8.2.x-dev » 8.3.x-dev

Drupal 8.2.6 was released on February 1, 2017 and is the final full bugfix release for the Drupal 8.2.x series. Drupal 8.2.x will not receive any further development aside from critical and security fixes. Sites should prepare to update to 8.3.0 on April 5, 2017. (Drupal 8.3.0-alpha1 is available for testing.)

Bug reports should be targeted against the 8.3.x-dev branch from now on, and new development or disruptive changes should be targeted against the 8.4.x-dev branch. For more information see the Drupal 8 minor version schedule and the Allowed changes during the Drupal 8 release cycle.

Version: 8.3.x-dev » 8.4.x-dev

Drupal 8.3.6 was released on August 2, 2017 and is the final full bugfix release for the Drupal 8.3.x series. Drupal 8.3.x will not receive any further development aside from critical and security fixes. Sites should prepare to update to 8.4.0 on October 4, 2017. (Drupal 8.4.0-alpha1 is available for testing.)

Bug reports should be targeted against the 8.4.x-dev branch from now on, and new development or disruptive changes should be targeted against the 8.5.x-dev branch. For more information see the Drupal 8 minor version schedule and the Allowed changes during the Drupal 8 release cycle.

Version: 8.4.x-dev » 8.5.x-dev

Drupal 8.4.4 was released on January 3, 2018 and is the final full bugfix release for the Drupal 8.4.x series. Drupal 8.4.x will not receive any further development aside from critical and security fixes. Sites should prepare to update to 8.5.0 on March 7, 2018. (Drupal 8.5.0-alpha1 is available for testing.)

Bug reports should be targeted against the 8.5.x-dev branch from now on, and new development or disruptive changes should be targeted against the 8.6.x-dev branch. For more information see the Drupal 8 minor version schedule and the Allowed changes during the Drupal 8 release cycle.

Version: 8.5.x-dev » 8.6.x-dev

Drupal 8.5.6 was released on August 1, 2018 and is the final bugfix release for the Drupal 8.5.x series. Drupal 8.5.x will not receive any further development aside from security fixes. Sites should prepare to update to 8.6.0 on September 5, 2018. (Drupal 8.6.0-rc1 is available for testing.)

Bug reports should be targeted against the 8.6.x-dev branch from now on, and new development or disruptive changes should be targeted against the 8.7.x-dev branch. For more information see the Drupal 8 minor version schedule and the Allowed changes during the Drupal 8 release cycle.

Version: 8.6.x-dev » 8.8.x-dev

Drupal 8.6.x will not receive any further development aside from security fixes. Bug reports should be targeted against the 8.8.x-dev branch from now on, and new development or disruptive changes should be targeted against the 8.9.x-dev branch. For more information see the Drupal 8 and 9 minor version schedule and the Allowed changes during the Drupal 8 and 9 release cycles.

jonathan1055’s picture

jonathan1055 CreditAttribution: jonathan1055 as a volunteer commented

I know we can achieve this with hook_query_TAG_alter() but it would be nice to have a solution built in.

as @daveReid said, way back,

orderRandom is already a part of SelectQueryInterface, just need to extend it to EFQ, but I'm not sure it can be done in a cross-platform way like DBTNG.

But a good many years and versions have passed since then, so are there any new ideas now?

Version: 8.8.x-dev » 8.9.x-dev

Drupal 8.8.7 was released on June 3, 2020 and is the final full bugfix release for the Drupal 8.8.x series. Drupal 8.8.x will not receive any further development aside from security fixes. Sites should prepare to update to Drupal 8.9.0 or Drupal 9.0.0 for ongoing support.

Bug reports should be targeted against the 8.9.x-dev branch from now on, and new development or disruptive changes should be targeted against the 9.1.x-dev branch. For more information see the Drupal 8 and 9 minor version schedule and the Allowed changes during the Drupal 8 and 9 release cycles.

lindsay.wils’s picture

lindsay.wils CreditAttribution: lindsay.wils commented

I swear I used to have this working with the tag_alter, but no longer seems to be. Can anyone confirm if they have this still working in D 9.2.6?

Thanks

Version: 8.9.x-dev » 9.2.x-dev

Drupal 8 is end-of-life as of November 17, 2021. There will not be further changes made to Drupal 8. Bugfixes are now made to the 9.3.x and higher branches only. For more information see the Drupal core minor version schedule and the Allowed changes during the Drupal core release cycle.

Version: 9.2.x-dev » 9.3.x-dev

Version: 9.3.x-dev » 9.4.x-dev

Drupal 9.3.15 was released on June 1st, 2022 and is the final full bugfix release for the Drupal 9.3.x series. Drupal 9.3.x will not receive any further development aside from security fixes. Drupal 9 bug reports should be targeted for the 9.4.x-dev branch from now on, and new development or disruptive changes should be targeted for the 9.5.x-dev branch. For more information see the Drupal core minor version schedule and the Allowed changes during the Drupal core release cycle.

Version: 9.4.x-dev » 9.5.x-dev

Drupal 9.4.9 was released on December 7, 2022 and is the final full bugfix release for the Drupal 9.4.x series. Drupal 9.4.x will not receive any further development aside from security fixes. Drupal 9 bug reports should be targeted for the 9.5.x-dev branch from now on, and new development or disruptive changes should be targeted for the 10.1.x-dev branch. For more information see the Drupal core minor version schedule and the Allowed changes during the Drupal core release cycle.

agoradesign’s picture

agoradesign CreditAttribution: agoradesign commented

Hey Lindsay,
my answer comes a little bit late. for me, it still works. I've just used that again for a current project on 9.5.3

Version: 9.5.x-dev » 11.x-dev

Drupal core is moving towards using a “main” branch. As an interim step, a new 11.x branch has been opened, as Drupal.org infrastructure cannot currently fully support a branch named main. New developments and disruptive changes should now be targeted for the 11.x branch. For more information, see the Drupal core minor version schedule and the Allowed changes during the Drupal core release cycle.