如何在laravel中使用辅助键获取记录

Question

我已经搜索了解决方案，但找不到任何可以完美解决这个问题的方法。我有两个模型，如下所示class Payments extends Model{    protected $table = 'payments';    public function investment_()    {        return $this->belongsTo('App\Investments', 'investment_id', 'id');    }}和class Investments extends Model{    protected $table = 'investments';    public function payments_()    {        return $this->hasMany('App\Payments', 'investment_id', 'id');    }}付款有一个金额列，而投资也有一个金额列。只有在特定投资的总付款至少是投资金额的一半的情况下，我如何才能提取所有付款？我不知道如何构造查询，以便付款可以选择相应的投资价值并检查条件。这就是我到目前为止在我的控制器中所拥有的 $payments = Payments::where('amount', '>=', Investment::where('id', Payments::where(?)))->get();注意：我不知道在问号中输入什么内容，也不知道我的方法是否正确。这是一个示例错误查询，可以更好地说明它SELECT a.SUM(a.amount) AS amount FROM payments as a, investments as b where a.investment_id = b.id and a.sum(a.amount)*2 = b.amount GROUP BY a.investment_id;

Answer 1

查询只是

SELECT a.SUM(a.amount) AS amount 

FROM payments as a JOIN investments as b 

     ON a.investment_id = b.id 

GROUP BY a.investment_id

HAVING SUM(a.amount)*2 < b.amount;

你可以在 Laravel Query builder 元素中轻松翻译，但如果你只需要这个，你可以使用DB::select('query')来获取它们

Answer 2

希望这能解决您的问题。请检查。谢谢

\App\Payment::where(function ($query) {

            $query->whereRaw('(select SUM(payment_amount) from payments as payments_final where investment_id = payments.investment_id) >= (select investment_amount/2 from investments where id=payments.investment_id)');

        })

        ->get();