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
What Are the Disadvantages of Using Persistent Connection in Pdo
How to Make Pdf File Downloadable in HTML Link
Are Arrays in PHP Copied as Value or as Reference to New Variables, and When Passed to Functions
How to Enable Curl in PHP/Xampp
Get PHP to Stop Replacing '.' Characters in $_Get or $_Post Arrays
How to Pass Data to View in Laravel
Set Up the Base Url in Codeigniter
PHP Function to Make Slug (Url String)
PHP/MySQL Insert Row Then Get 'Id'
Get Url Query String Parameters
MySQLi Prepared Statements Error Reporting
Passing $_Post Values With Curl
How to Add/Remove Pkcs7 Padding from an Aes Encrypted String
Method="Post" Enctype="Text/Plain" Are Not Compatible