Eloquent |添加要选择的自定义值

Question

我有多个表，它们都保存不同的事务数据，下面是一个例子:

$q1 = DB::connection('mysql_live')->table('transactions_vk')->where('user_id', $userid)->where('amount', '!=', '0')->where('payed', 'Yes')
->select('type', 'id', 'package', 'payed', 'amount', 'date_modified as date_added', 'payed as chargeid')->get(); 
$q2 = DB::connection('mysql_live')->table('transactions_stripe')->where('user_id', $userid)->where('amount', '!=', '0')->where('payed', 'Yes')
->select('type', 'id', 'package', 'payed', 'amount', 'date_added', 'charge_id as chargeid')->get();
$total1 = $q1->merge($q2);

我查询它们并将它们合并为一个结果。当我查询它们时，我知道它们是哪种支付类型，在我的例子中，q1是所有VK交易，q2是所有stripe交易。

有办法在这里添加这个信息吗?

像q1的payment_type: VK和q2的payment_type: Stripe?

那么结果数组中每个item的信息都是这样的，item看起来是这样的:

amount: 8999
chargeid: "pi_chargeid"
date_added: "2021-09-10 10:47:29"
id: 70
package: 31
payed: "Yes"
type: "credits"
payment_type: "VK"

amount: 2495
chargeid: "123456789"
date_added: "2016-09-19 17:15:43"
id: 70405
package: 11
payed: "No"
payment_type: "Stripe"

Answer 1

在您的情况下，您可以这样编写代码:

$q1 = DB::connection('mysql_live')->table('transactions_vk')->where('user_id', $userid)->where('amount', '!=', '0')->where('payed', 'Yes')
->select(DB::raw('"VK" AS payment_type, type, id, package, payed, amount, date_modified as date_added, payed as chargeid'))->get();

$q2 = DB::connection('mysql_live')->table('transactions_stripe')->where('user_id', $userid)->where('amount', '!=', '0')->where('payed', 'Yes')
->select(DB::raw('"Stripe" AS payment_type, type, id, package, payed, amount, date_added, charge_id as chargeid'))->get();

$total1 = $q1->merge($q2);

Answer 2

您可以在select中添加一个列，方法与在纯SQL中类似:

$q1 = DB::connection('mysql_live')->table('transactions_vk')->where('user_id', $userid)->where('amount', '!=', '0')->where('payed', 'Yes')
->select('type', 'id', 'package', 'payed', 'amount', 'date_modified as date_added', 'payed as chargeid', DB::raw("'VK' as 'payment_type'"))->get(); 
$q2 = DB::connection('mysql_live')->table('transactions_stripe')->where('user_id', $userid)->where('amount', '!=', '0')->where('payed', 'Yes')
->select('type', 'id', 'package', 'payed', 'amount', 'date_added', 'charge_id as chargeid',DB::raw("'Stripe' as 'payment_type'"))->get();
$total1 = $q1->merge($q2);

更新:你的查询似乎是联合兼容的，所以你可以节省自己的查询做:

$q1 = DB::connection('mysql_live')->table('transactions_vk')->where('user_id', $userid)->where('amount', '!=', '0')->where('payed', 'Yes')
->select('type', 'id', 'package', 'payed', 'amount', 'date_modified as date_added', 'payed as chargeid', DB::raw("'VK' as 'payment_type'")); 
$q2 = DB::connection('mysql_live')->table('transactions_stripe')->where('user_id', $userid)->where('amount', '!=', '0')->where('payed', 'Yes')
->select('type', 'id', 'package', 'payed', 'amount', 'date_added', 'charge_id as chargeid',DB::raw("'Stripe' as 'payment_type'"));
$total1 = $q1->union($q2)->get();