Laravel Eloquent - Select all columns plus a subquery using Eloquent only

user_5321660 0
Viewed 220

I need to select all columns from a table and an additional columns using subquery in laravel's eloquent. For example,

SELECT *, (SELECT COUNT(*) FROM transactions WHERE transactions.customer=customers.id) AS transactions FROM customers

Right now, I have come up with using a raw query using selectRaw like:

$customers = Customer::selectRaw('*, (SELECT COUNT(*) FROM transactions WHERE transactions.customer=customers.id) AS transactions')->get();

But I'd like to do it the eloquent way, without using raw queries.

laravel-5 eloquent
1 Answers

as i see here you have 1-N relation between customer and transaction , if you have set up your models with eloquent relationships belongTo and hasMany , then you can use the With for eager load subqueries :

Eloquent eager load and eager load count relation

//customers + transactions for each one
$customers = Customer:with('transactions')->get();

// customers + transaction count for each one
$customers = Customer:withCount('transactions')->get();

Customer Model

<?php

namespace App\Models;

use Illuminate\Database\Eloquent\Model;

class Customer extends Model
{
    /**
     * Get the transactions for the customer.
     */
    public function transactions()
    {
        return $this->hasMany(Transaction::class);
    }
}

Transaction Model

<?php

namespace App\Models;

use Illuminate\Database\Eloquent\Model;

class Transaction extends Model
{
    /**
     * Get the customer of this transaction.
     */
    public function customer()
    {
        return $this->belongsTo(Customer::class);
    }
}
user_158618620
Related