• 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)])