laravel,  php,  phpmyadmin

Laravel join sql query syntax with where between group by

Its a query to join two tables and get the details in between given date format ,according to month (month wise) and of specific city .

sample work

Simple SQL query

the query in simple SQL to be added in any language .

select count(*) as count , city , MONTH(`abctable`.`created_at`) as mm, YEAR(`abctable`.`created_at`)as yy from `abctable` join `xyztable` on `abctable`.`entity_id` = `xyztable`.`parent_id` where `abctable`.`created_at` between '2019-04-24 00:00:00' and '2019-09-12 12:12:12' AND `xyztable`.`city`='Pune' GROUP BY YEAR(`abctable`.`created_at`),MONTH(`abctable`.`created_at`)

Laravel SQL query

the sql query in laravel controller form data.

 $users = DB::table('abctable')
        ->select(DB::raw('count(*) as count, MONTH(abctable.created_at) as MM, YEAR(abctable.created_at) as YY'))
        ->join('xyztable', 'n3d_sales_flat_order_grid.entity_id', '=', 'xyztable.parent_id')
        ->whereBetween('abctable.created_at',[new Carbon($fdate),new Carbon($gdate)])
        ->where('xyztable.city','like',$city)
        ->groupBy(DB::raw('MONTH(abctable.created_at)'),DB::raw('YEAR(abctable.created_at)'))
        ->get();

Leave a Reply

Your email address will not be published. Required fields are marked *