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

Ajith Gopi

I need to select all columns from a table and an additional columns using subquery. For example,

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

Right now, I have come up with 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.

HijenHEK

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);
    }
}

Collected from the Internet

Please contact [email protected] to delete if infringement.

edited at2021-06-4

Comments

0 comments

Laravel Eloquent WHERE field=(...) subquery

laravel eloquent select dynamic