How to Do This in Laravel, Subquery Where In

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

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 do this in laravel using Laravel subquery with groupby and where

You can use closure as the second parameter of the whereIn method.

whereIn($column, $values, $boolean = 'and', $not = false)

$return = DB::table('eplan_vacancy')
    ->whereIn('reference_id', function ($query) {
        return $query->select('eplan_ep_details.reference_id')
            ->from('eplan_ep_details')
            ->join('eplan_court_cases', 'eplan_ep_details.reference_id', '=', 'eplan_court_cases.reference_id')
            ->where('ep_cc_status', 1)
            ->groupBy('eplan_court_cases.reference_id');
    })
    ->where('is_form_submitted', 1)
    ->whereIn('st_code', ['U05', 'S13', 'S01'])
    ->groupBy('reference_id')
    ->orderBy('created_at', 'desc');

dd($return->toSql());

Result

select * from `eplan_vacancy` where `reference_id` in 
    (
        select `eplan_ep_details`.`reference_id` 
        from `eplan_ep_details` 
        inner join `eplan_court_cases` on `eplan_ep_details`.`reference_id` = `eplan_court_cases`.`reference_id` 
        where `ep_cc_status` = ? 
        group by `eplan_court_cases`.`reference_id`
    ) 
and `is_form_submitted` = ? 
and `st_code` in (?, ?, ?) 
group by `reference_id` 
order by `created_at` desc

Laravel Eloquent ORM WHERE IN (subquery)

Lets simplify your code to.

$arr = \App\tableB::where("other_field", "=", $value)->lists('id')->all(); 

$res = \App\tableA::whereIn("field", $arr)->get();

The lists() chained with all() will automatically convert your collection to an array. But wit laravel 5.0 or less you dont need the all() to convert your collection to an array.

How to select from subquery using Laravel Query Builder?

In addition to @delmadord's answer and your comments:

Currently there is no method to create subquery in FROM clause, so you need to manually use raw statement, then, if necessary, you will merge all the bindings:

$sub = Abc::where(..)->groupBy(..); // Eloquent Builder instance

$count = DB::table( DB::raw("({$sub->toSql()}) as sub") )
    ->mergeBindings($sub->getQuery()) // you need to get underlying Query Builder
    ->count();

Mind that you need to merge bindings in correct order. If you have other bound clauses, you must put them after mergeBindings:

$count = DB::table( DB::raw("({$sub->toSql()}) as sub") )

    // ->where(..) wrong

    ->mergeBindings($sub->getQuery()) // you need to get underlying Query Builder

    // ->where(..) correct

    ->count();

Using Laravel query builder method whereIn() with sub query

refer this

 $courseId = 1;

 $data = DB::table('student')->whereIn('id',function($query) use (courseId){
    $query->select('student_id')
            ->from('student_course')->where('course_id', '=',$courseId);
})->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.

How to do this query with Eloquent / Laravel: subquery WHERE IN with GROUP BY

@techienomics you may try this;

$output = TableName::select('*')
           ->whereRaw('id1 in (SELECT MAX(id2) FROM tableName group by var1,var2,var3)')
           ->get();

I hope it helps.

How to do this in Laravel, subquery where not in

You have a typo:

->whereNotIn('erp_product.erp_producid'

Should be:

->whereNotIn('erp_product.erp_productid'

Notice the misspelled productid.

---

One thing you can do to debug is to remove the ->get() call so that $categoria is a query object, and then do this so you can see the assembled query:

dd($categoria->toSql());

---

Related Topics