Как извлечь данные из одной таблицы на основе вычисления двух других таблиц?

#php #mysql #sql #database #laravel

#php #mysql #sql #База данных #laravel

Вопрос:

Предположим, у меня есть три модели, названные как Customer ,Invoice и Payment . Модель счета и оплаты выглядит следующим образом

id , customer_id, amount

Я хочу получить только тех клиентов, у которых Invoice.sum(amount)>Payment.sum(amount) разница в этих суммах

В настоящее время я извлекаю как

 $customers=Customer::get();
foreach($customers as $customer)
{  
  $pay=Payment::where('customer_id',$customer->id)->sum('amount');
  $due=Invoice::where('customer_id',$customer->id)->sum('amount');
  if($due>$pay){
   // showing this customers 
   }
}

Есть ли лучший способ с eloquent join?
Как я могу получить в laravel eloquent?

1. Не могли бы вы показать нам столбцы таблиц и их взаимосвязи?

2. @Miggy customer_id — это внешний ключ как в модели оплаты, так и в модели счета, который является первичным ключом в модели клиента

3. @Miggy Оба столбца модели счета и платежа: id, customer_id, amount

Ответ №1:

Вы установили какие-либо отношения в модели? Более красноречивый запрос будет выглядеть следующим образом. Возможно, вам потребуется немного скорректировать

 Customer::join('payment','customer.id','=','payment.customer_id')
          ->join('invoice','customer.id','=','invoice.customer_id')
          ->select(array('customer.*'),DB::raw("SUM(payment.amount) as payment_sum,SUM(invoice.amount) as invoice_sum"))
          //->where('customer_id',$customer->id)
          ->groupBy('customer.id')  //replace with anything that make sense for you.
          ->havingRaw('invoice_sum > payment_sum')
          ->get();

1. kean В исходном запросе mysql работает нормально, просто удалив предложение where(‘customer_id’,$customer-> id), но в larave это не работает. Это показывает синтаксическую ошибку или нарушение доступа: 1140 Смешивание ГРУППОВЫХ столбцов (MIN(), MAX (), COUNT (), …) без ГРУППОВЫХ столбцов является незаконным, если нет предложения GROUP BY

2. я думаю, вам нужно добавить groupby(‘column’) перед использованием havingRaw(), чтобы посмотреть, работает ли это

3. КИН Да, это сработает, если я добавлю groupBy(‘customers.id ‘) но это показывает мне customers.name не в group by . Как я могу это решить. обратите внимание, что я также установил ‘strict’ => false, в database.php

4. @kean все еще есть ошибка 1052 Столбец ‘customer_id’ в операторе group неоднозначен

5. Если я использую ‘customer.id ‘ вместо ‘customer.*’ все работает нормально. Но мне также нужны данные таблицы клиентов

Ответ №2:

Попробуйте это

Сначала определите взаимосвязь в вашей Customer модели

 public function payments()
{
  return $this->hasMany(Payment::class); //based on your logic or structure
}

public function invoices()
{
  return $this->hasMany(Invoice::class); //based on your logic or structure
}

 Customer::with(['payments' => function($query) {
               $query->sum('amount');
         }])
        ->get();

или

 $customers=Customer::with('payments','invoices')->get();
foreach($customers as $customer)
{  
  $pay = $customer->payments()->sum('amount');
  $due = $customer->invoices()->sum('amount');

   //other logic
}