时间戳

首页 > 解决方案 > Eloquent: Get all rows where column value(x) exists in more than one row (SQL DB)

php - Eloquent: Get all rows where column value(x) exists in more than one row (SQL DB)

问题描述

I want to perform the following raw SQL query in Laravel's Eloquent:

SELECT distinct user_id FROM order_payment WHERE user_id IN (SELECT user_id FROM order_payment where payment_status = 'paid' GROUP BY user_id HAVING COUNT(*) > 1);

here I am using where in with property user_id such that all rows in the same table(order_payment) having payment_status=paid and grouped by property user_id having count > 1 are selected and then finally we select distinct user_ids from the given set of user ids.

The overall objective of this query is to get all the rows where required column value(user_id's in this case) > 1...

Ex: 

we have 3 rows > A, B, C
row A has user_id = 1;
row B has user_id = 1;
row C has user_id = 2;

result should have [user_id: 1] as the count becomes greater than 1

The above raw query work for me, but now that i am writing a api, I was wondering how this can be replicated in Eloquent.

PLEASE HELP!

标签： phpeloquentlaravel-5.6eloquent-relationship

解决方案

所以这个查询的雄辩方式可以使用groupByandhavingRaw方法完成

我们首先选择需要聚合的列

OrderPayment::select('user_id', \DB::raw('COUNT(user_id) as payment_made))

然后我们 groupBy 我们需要的 arribute - groupBy('user_id')，注意我们分组的属性必须在 select 语句中，否则这将不起作用。

现在我们查看是否有多个行具有相同的最后一个子句user_id，我们使用 >havingRaw('COUNT(user_id) > ?', [1])

现在整个 eloquent 查询看起来像这样

OrderPayment::select('user_id', \DB::raw('COUNT(user_id) as payment_made))
    ->groupBy('user_id')
    ->havingRaw('COUNT(user_id) > ?', [1])
    ->get();

请注意，在 select 子句中，我们将 DB 原始语句作为字符串传递给查询构建器，因此应该小心 SQL 注入。

推荐阅读