如何在 Laravel 中获取非键列字段的不同值？

How to get distinct values for non-key column fields in Laravel?

提问by gthuo

This might be quite easy but have no idea how to.

这可能很容易，但不知道如何做。

I have a table that can have repeated values for a particular non-key column field. How do I write a SQL query using Query Builder or Eloquent that will fetch rows with distinct values for that column?

我有一个表，可以为特定的非键列字段包含重复的值。如何使用 Query Builder 或 Eloquent 编写 SQL 查询，该查询将为该列获取具有不同值的行？

Note that I am not fetching that column only, it is in conjunction with other column values, so distinct()might not really work. So that question basically can be how to specify the column that I want to be distinct in a query now that distinct()accepts no parameters?

请注意，我不是仅获取该列，它与其他列值结合使用，因此distinct()可能不会真正起作用。所以这个问题基本上可以是如何在distinct()不接受任何参数的查询中指定我想要区分的列？

回答by Marcin Nabia?ek

You should use groupby. In Query Builder you can do it this way:

你应该使用groupby. 在查询生成器中，您可以这样做：

$users = DB::table('users')
            ->select('id','name', 'email')
            ->groupBy('name')
            ->get();

回答by Pathros

In Eloquent you can also query like this:

在 Eloquent 中，您还可以这样查询：

$users = User::select('name')->distinct()->get();

$users = User::select('name')->distinct()->get();

回答by Salar

in eloquent you can use this

雄辩地你可以使用这个

$users = User::select('name')->groupBy('name')->get()->toArray() ;

groupBy is actually fetching the distinct values, in fact the groupBy will categorize the same values, so that we can use aggregate functions on them. but in this scenario we have no aggregate functions, we are just selecting the value which will cause the result to have distinct values

groupBy 实际上是获取不同的值，实际上 groupBy 将对相同的值进行分类，以便我们可以对它们使用聚合函数。但在这种情况下，我们没有聚合函数，我们只是选择将导致结果具有不同值的值

回答by rsakhale

Though I am late to answer this, a better approach to get distinct records using Eloquent would be

虽然我回答这个问题晚了，但使用 Eloquent 获取不同记录的更好方法是

$user_names = User::distinct()->get(['name']);

回答by Jed Lynch

Grouping by will not work if the database rules don't allow any of the select fields to be outside of an aggregate function. Instead use the laravel collections.

如果数据库规则不允许任何选择字段位于聚合函数之外，则分组依据将不起作用。而是使用laravel 集合。

$users = DB::table('users')
        ->select('id','name', 'email')
        ->get();

foreach($users->unique('name') as $user){
  //....
}

Someone pointed out that this may not be great on performance for large collections. I would recommend adding a key to the collection. The method to use is called keyBy. This is the simple method.

有人指出，这对于大型集合的性能可能不是很好。我建议在集合中添加一个键。要使用的方法称为keyBy。这是简单的方法。

     $users = DB::table('users')
        ->select('id','name', 'email')
        ->get()
        ->keyBy('name');

The keyBy also allows you to add a call back function for more complex things...

keyBy 还允许您为更复杂的事情添加回调函数......

     $users = DB::table('users')
        ->select('id','name', 'email')
        ->get()
        ->keyBy(function($user){
              return $user->name . '-' . $user->id;
         });

If you have to iterate over large collections, adding a key to it solve the performance issue.

如果您必须迭代大型集合，向它添加一个键可以解决性能问题。

回答by Mark-Hero

$users = User::select('column1', 'column2', 'column3')->distinct()->get();retrieves all three coulmns for distinct rows in the table. You can add as many columns as you wish.

$users = User::select('column1', 'column2', 'column3')->distinct()->get();检索表中不同行的所有三个库姆。您可以根据需要添加任意数量的列。

回答by jec006

Note that groupByas used above won't work for postgres.

请注意，groupBy上面使用的不适用于 postgres。

Using distinctis probably a better option - e.g. $users = User::query()->distinct()->get();

使用distinct可能是一个更好的选择 - 例如 $users = User::query()->distinct()->get();

If you use queryyou can select all the columns as requested.

如果您使用，query您可以根据要求选择所有列。

回答by Robby Alvian Jaya Mulia

**

**

Tested for Laravel 5.8

已针对 Laravel 5.8 进行测试

**

**

Since you wanna get all columns from the table, you can collect all of the data and then filter it using Collections function called Unique

由于您想从表中获取所有列，您可以收集所有数据，然后使用名为Unique 的Collections 函数对其进行过滤

// Get all users with unique name
User::all()->unique('name')

or

或者

// Get all & latest users with unique name 
User::latest()->get()->unique('name')

For more information you can check Laravel Collection Documentations

有关更多信息，您可以查看Laravel 集合文档

EDIT: You might have issue with perfomance, by using Unique() you'll get all data first from User table, and then Laravel will filter it. This way isn't good if you have lots of Users data. You can use query builder and call each fields that you wanna use, example:

编辑：您可能会遇到性能问题，通过使用 Unique()，您将首先从 User 表中获取所有数据，然后 Laravel 将对其进行过滤。如果您有大量用户数据，这种方式并不好。您可以使用查询构建器并调用您想要使用的每个字段，例如：

User::select('username','email','name')->distinct('name')->get();

回答by Latheesan

I found this method working quite well (for me) to produce a flat array of unique values:

我发现这种方法（对我来说）效果很好，可以生成唯一值的平面数组：

$uniqueNames = User::select('name')->distinct()->pluck('name')->toArray();

If you ran ->toSql()on this query builder, you will see it generates a query like this:

如果您->toSql()在此查询构建器上运行，您将看到它生成如下查询：

select distinct `name` from `users`

The ->pluck()is handled by illuminate\collection lib (not via sql query).

的->pluck()是通过照射\收集LIB（不是通过SQL查询）来处理。

回答by Roland Verner

$users = Users::all()->unique('name');