Category : group-by

I have a somewhat unusual problem and I’ve not been able to fully solve it. I have the following statement, which on our local development branch (using MySQL) is working correctly and showing the info that’s intended. $productos = Producto::select( [ ‘productos.*’, DB::raw(‘COUNT(lotes.stock_actual) as stock_actual’), DB::raw(‘COUNT(stock_lote_reserva.cantidad) as reserva_actual’), DB::raw(‘COUNT(lotes.stock_actual)+COUNT(stock_lote_reserva.cantidad) as cantidad_fisica_total’) ] )->leftJoin(‘lotes’, function ($join) ..

Read more

I want to group by table by name and select name and date, for name with same values have deferent valus of date, I tried this but don’t work : public function period(Request $req) { $absences = Absence::whereBetween(‘date’, [$req->from, $req->to])->get(); $daily = $absences->select(‘name ‘,’date’, ‘COUNT(date) as counts’); $arr[‘absences’] = $absences; $arr[‘daily’] = $daily; $arr[‘from’] = ..

Read more

I have the following two tables: I’m tryin to get something like : adm_email1[ nom_projet1 nom_projet2 …… ], adm_email2[ nom_projet3 nom_projet4 …… ] So I tried the following query usinh Laravel 8 : $result = Projet::join(‘filiales’,’projets.id_filiale’,’=’,’filiales.id_filiale’)->select(‘projets.nom_projet’,’filiales.adm_email’)->groupBy(‘filiales.adm_email’)->get(); But I don’t get the result that I wanted. If you have any idea please help. Sourc..

Read more

I’m trying to sort the products by SKU, but I’m getting multiple results On my controller: function list($type) { if ($type == ‘in_house’) { $pro = Product::where([‘added_by’ => ‘admin’])->groupBy(‘sku’)->latest()->paginate(25); } else { $pro = Product::where([‘added_by’ => ‘seller’])->groupBy(‘sku’)->latest()->paginate(25); } return view(‘admin-views.product.list’, compact(‘pro’)); } On my blade @foreach ($pro as $sku => $p) <p>{{$p[‘sku’]}}</p> <ul class="list-unstyled"> @foreach($p ..

Read more

I have two models and merged them and try to groupby them with sum of column: public function getCards() { $home_cards_games=Game::groupBy(‘home_club_id’)->selectRaw(‘ home_club_id ,SUM(home_cards) as cards’)->orderBy(‘cards’,’desc’)->get(); foreach($home_cards_games as $home_cards_game){ $home_cards_game->club=$home_cards_game->homeclub->club; unset($home_cards_game->homeclub); } $away_cards_games=Game::groupBy(‘away_club_id’)->selectRaw(‘ away_club_id ,SUM(away_cards) as cards’)->orderBy(‘cards’,’desc’)->get(); foreach($away_cards_games as $away_cards_game){ $away_cards_game->club=$away_cards_game->awayclub->club; unset($away_cards_game->awayclub); } $all_cards=collect(); foreach($home_cards_games as $home_cards_game){ $all_cards->push($home_cards_game); } foreach($away_cards_games as $away_cards_game){ $all_cards->push($away_cards_game); } $all_cards=$all_cards->groupBy(‘club’)->selectRaw(‘ club ..

Read more

i have two joined tables.. i need to sum all rows but when i use code DB::raw(‘sum(items.it_price_egp_in) As sum1’), i just get the selected value in items.it_price_egp_in not sum of all rows please check comment in code here is controller code $data = Item::select(‘items.it_photo_name’,’items.it_arabic_name’,’items.it_english_name’,’items.it_cod_1′,’items.id’,’items.shp_no_for_it’,’items.it_buy_date’,’items.it_qut_1′,’items.it_created_date’, ‘items.it_buy_date’,’items.it_storaged_date’,’items.it_out_of_stock’,’items.it_price_egp_in’,’items.it_other_fees_in’,’items.it_price_usd_out’,’items.it_supp_shipping_fees_out’,’items.it_other_fees_out’, ‘shipments.shp_destination_shipping_fees’,’shipments.shp_customs_fees’,’shipments.shp_permission_receive_fees’,’shipments.shp_storage_fees’,’shipments.shp_mid_fees’,’shipments.shp_other_fees_out’, ‘shipments.shp_clearance_fees’,’shipments.shp_trans_fees_out’,’shipments.shp_trans_fees_in’,’shipments.shp_other_fees_in’ , ‘shipments.shp_no’, // sum(items.it_price_egp_in) not working good just ..

Read more

I have a bunch of ToDo items, some of which are recurring. The same task may have a due_date every week/month/quarter etc. I’m tracking recurring ToDo items with a recurring_identifier_id. E.g. the January and February version of a ToDo task of would have the same recurring_identifier_id to link them. I want to query the database ..

Read more

$lists = DB::table(‘searches’)->orderBy(‘query’, ‘DESC’)->paginate(15); @foreach ($lists as list) <div> {{$list->name}} </div> @endforeach I did write this query and It shows: Apple Apple Apple Apple Apple Apple Banana Banana Banana Grapes Grapes Orange …. I want to show only the names of the fruits having an overall count of occurences greater than 3 (if count>3) I ..

Read more

$purchasers = DB::table(‘orders’) ->join(‘users as buyers’, ‘buyers.user_id’, ‘=’, ‘orders.user_id’) ->join(‘order_items’,’order_items.order_id’,’=’,’orders.order_id’) ->join(‘stocks’,’stocks.stock_id’,’=’,’order_items.stock_id’) ->join(‘sizes’,’sizes.size_id’,’=’,’stocks.size_id’) ->join(‘shoes’,’shoes.shoe_id’,’=’,’stocks.shoe_id’) ->where(‘orders.status’, ‘2’) ->where(‘orders.completed_date’, ‘>=’, $start) ->where(‘orders.completed_date’, ‘<=’, $end) ->select(DB::raw(‘CONCAT(buyers.last_name, ", ", buyers.first_name) as user_fullName’),DB::raw(‘order_items.quantity * shoes.price as amount’), ‘orders.order_id as order_id’ ) Sourc..

Read more

I am trying to execute the next SQL query: SELECT sex, recuperated, count(case_id) as total_cases FROM api.patients GROUP BY recuperated, sex ORDER BY total_cases DESC; Which returns: ‘41523’, ‘ANTIOQUIA’, ‘MEDELLIN’ ‘6066’, ‘ANTIOQUIA’, ‘BELLO’ ‘6056’, ‘AMAZONAS’, ‘LETICIA’ ‘3855’, ‘ANTIOQUIA’, ‘ITAGUI’ first in eloquent: $query = Patient::query(); $statistics[0] = $query ->select("sex", "recuperated", "count (case_id) as total_cases") ->orderBy("total_cases","desc") ..

Read more

i have 3 row data in database , i want query to make it become 1 data by its id , what should i do to get 1 row content like : |operator_id|durasi_jam_kerja|durasi_jam_lembur|durasi_jam_standby|         9                                                    2                               8 Sourc..

Read more

In my application, I’ve used the Sentinel package for authorization. Now I need an API to retrieve user permissions for an expected JSON structure. expected JSON structure: { "user_id": 3172, "first_name": "Bonny", "last_name": "Hewage", "username": "bonny", "user_roles": [ { "id": 2, "slug": "store-user", "name": "Store User", "permissions": {"user.create":true, "user.update":true} }, { "id": 6, "slug": "mu-supervisor", ..

Read more

I have these models below : season : public function club() { return $this->hasMany(‘AppModelsClub’); } club : public function season() { return $this->belongsTo(‘AppModelsSeason’ ,’season_id’); } public function league() { return $this->belongsTo(‘AppModelsLeague’ ,’league_id’); } league : public function club() { return $this->hasMany(‘AppModelsClub’); } the season have column called status which take value of ‘active’ or empty. ..

Read more

I am not getting my desired output when using group by and order by in laravel here is my code $old=DB::table(‘tbl1’) ->leftJoin(‘tbl2′,’tbl1.id’,’=’,’tbl2.tbl1_id’) ->whereDate(‘tbl2.created_at’,'<‘ ,Carbon::today()) ->where(‘tbl1.users_id’,$id) ->select(‘tbl2.name as name’,’tbl1.teacher as teacher’,’table2.class_id as class_id’) ->orderBy(‘tbl2.created_at’,’desc’) ->groupBy(‘tbl2.class_id’) ->get() ->toArray(); group by working fine but it not returning me the latest value i just want to get latest value ..

Read more

I have a table for Specifications and the table contains specification_key , specification_value. I’m trying to group all specifications by the specification_key and also each group must contain only unique values. I tried: $specifications = Specification::whereIn(‘product_id’, $products->pluck(‘id’))->get(); $SpecsGroup = $specifications->groupBy(‘specification_key’); dd($SpecsGroup); This returns groups by specification_key but these contain the same specification_value multiple times. I ..

Read more

I need help in getting the max data_date of a table that is grouped by report_type_id in laravel.So in order to get this i wrote my query like this $latest_reports = Filelist:: whereRaw(‘data_date in (select max(data_date) from filelists GROUP BY (report_type_id))’) ->where(‘access_id’,’=’,$retailer_supplier_id->id) ->get(); Here is my table please have a look id access_id filename report_type_id ..

Read more

Here i want to fetch results based on the maximum date from the field so in order to get that i wrote query like this $latest_reports = Filelist:: select(‘report_type_id’,DB::raw(‘filename,max(data_date) as latest_date’)) ->where(‘access_id’,’=’,$retailer_supplier_id->id) ->groupBy(‘report_type_id’) ->orderBy(‘data_date’,’desc’) ->get(); Here is my table please have a look id access_id filename report_type_id data_date 1 16 filename1 6 2021-02-01 2 16 ..

Read more