How to Create a Subquery Using Laravel Eloquent

How to create a subquery using Laravel Eloquent?

This is how you do a subquery where:

$q->where('price_date', function($q) use ($start_date)
{
   $q->from('benchmarks_table_name')
    ->selectRaw('min(price_date)')
    ->where('price_date', '>=', $start_date)
    ->where('ticker', $this->ticker);
});

Unfortunately orWhere requires explicitly provided $operator, otherwise it will raise an error, so in your case:

$q->orWhere('price_date', '=', function($q) use ($start_date)
{
   $q->from('benchmarks_table_name')
    ->selectRaw('min(price_date)')
    ->where('price_date', '>=', $start_date)
    ->where('ticker', $this->ticker);
});

---

EDIT: You need to specify from in the closure in fact, otherwise it will not build correct query.

How to do this in Laravel, subquery where in

Consider this code:

Products::whereIn('id', function($query){
    $query->select('paper_type_id')
    ->from(with(new ProductCategory)->getTable())
    ->whereIn('category_id', ['223', '15'])
    ->where('active', 1);
})->get();

Subquery in Laravel Eloquent

You're using select wrong. The select method accepts column names as parameters. It would also be best if you didn't use raw expressions because by doing that, you lose Laravel's escaping. Instead, you should leverage Laravel's query builder.

It should look something like this:

DB::table('tickets')
  ->select('*')
  ->whereIn('id', function($query) {
    $query->select('ticket_id')
      ->from('working_on')
      ->where('user_id', Auth::user()->id)
      ->whereNotIn('state', [0, 2]);
  })->orderByDesc('id')
  ->paginate(3);

By the way, the first select is redundant because * is the default value.

Also, consider querying the Ticket model directly instead of the database table or creating a WorkingOn model and using it.

Laravel Eloquent Subquery with condition

did you try the selectRaw or raw method?

something like this

    $task = DB::table('tasks')
        ->select(
            'tasks.*',
            'users.name as user_name',
        )
        ->when(Auth::check(), function($query) {
            return $query->addSelect(
                DB::raw('select count(id) from tasks_favorites where tasks_favorites.task_id=tasks.id AND tasks_favorites.user_id='.auth()->user()->id.' as mycount')
            );
        })
        ->leftJoin('users', 'users.id', 'tasks.user_id')
        ->where('tasks.id', $task_id)
        ->get()
        ->first() ;

Using subqueries in Eloquent/Laravel

You can do a subquery as a table but need to create the subquery first and then merge the bindings into the parent query:

$sub = Character::select('id', 'refreshToken', 'name')
    ->selectSub('MAX(`balances`.`created_at`)', 'refreshDate')
    ->join('balances', 'characters.id', '=', 'balances.character')
    ->whereNotNull('characters.refreshToken')
    ->groupBy('characters.id');

DB::table(DB::raw("($sub->toSql()) as t1"))
    ->mergeBindings($sub)
    ->where('refreshDate', '<', '2017-03-29')
    ->get();

If that is your entire query you can do it without the subquery and use having() instead like:

Character::select('id', 'refreshToken', 'name')
    ->selectSub('MAX(`balances`.`created_at`)', 'refreshDate')
    ->join('balances', 'characters.id', '=', 'balances.character')
    ->whereNotNull('characters.refreshToken')        
    ->groupBy('characters.id')
    ->having('refreshDate', '<', '2017-03-29');

How to Select from SubQuery using distinct in Laravels

You can use subquery tables as closures or passing them as builder objects.

• DB::table(Closure, alias) or DB::query()->from(Closure, alias)
• DB::table(Builder, alias) or DB::query()->from(Closure, alias)

$subquery = DB::table('warga')
    ->select('no_card', 'description')
    ->distinct();

$results = DB::table($subquery, 't')
    ->select('description')
    ->selectRaw('count(description) as cnt')
    ->groupBy('description')
    ->get();

$results = DB::table(function ($query) {
        $query->from('warga')
            ->select('no_card', 'description')
            ->distinct();
    }, 't')
    ->select('description')
    ->selectRaw('count(description) as cnt')
    ->groupBy('description')
    ->get();

You can even make the query look very SQL-like if you want.

$query = DB::query()
    ->select('description')
    ->selectRaw('count(description) as cnt')
    ->from(function ($sub) {
        $sub->select('no_card', 'description')
            ->distinct();
    }, 't')
    ->groupBy('description')
    ->get();

Sub query with eloquent relationship

Look like by default its not supported Eager-loading with a limit

To solve this problem you can install following library

https://github.com/staudenmeir/eloquent-eager-limit

and use following trait in both the model

use \Staudenmeir\EloquentEagerLimit\HasEagerLimit;

if you are looking for without library then

Ref:Laravel Eloquent limit results for relationship

Ref:Eager-loading with a limit on collection only loads for last element in collection

https://github.com/laravel/framework/issues/18014

Laravel Subquery with Query Builder

You should create a relationship between your User model and the model for the covis_transactions table. (I'm gonna call it CovisTransaction)

# User.php
public function covis_transactions()
{
    return $this->hasMany(CovisTransaction::class);
}

Then, you can use withCount to get the aggregate count.

User::query()
    ->select('id', 'nip', 'name')
    ->withCount('covis_transactions as total_survey')
    ->where('user_role_id', 7)
    ->groupBy('id')
    ->get();

---

• https://laravel.com/docs/9.x/eloquent-relationships#one-to-many
• https://laravel.com/docs/9.x/eloquent-relationships#aggregating-related-models

how to implement 3 nested subquery with 3 model in laravel?

in Transaction.php your relationship to carts:

public function carts()
{
    return $this->hasMany(Cart::class);
}

in Cart.php you relationship to accepted carts:

public function accepted_carts()
{
    return $this->hasMany(Acceptedcart::class);
}

And the query to get the transactions where acceptedcart->delivered == true:

$transactions = Transaction::whereHas('carts.accepted_carts', function ($query) {
    $query->where('delivered', true);
})->get();

---

Related Topics