How can I write this sql query in Laravel?

select `tableA`.*, `tableB`.`start_date`, `tableB`.`end_date`, `tableB`.`price`
  from `tableA` 
    right join(
    SELECT id, start_date, end_date, pro_id, price, DATEDIFF(`tableB`.`end_date`, '2021-03-07') diff
    FROM `tableB`
    GROUP BY id order by diff asc
  ) `tableB` on `tableA`.`id` = `tableB`.`pro_id`
      where (date(`end_date`) >= '2021-03-07')
        group by `tableA`.`id`
          order by `price` desc

I tried like this but it gave an error "Call to undefined method October\Rain\Database\QueryBuilder::joinSub()". What should I do for this? or how should a new query be? Can you give a clue about this?

$diffPrice = \DB::table('tableB')
                    ->select('id', 'end_date', 'pro_id', 'price', \DB::raw('DATEDIFF(`tableB`.`end_date`, 0) diff'))
                    ->where('is_published', true)
                    ->groupBy('user_id')
                    ->orderBy('diff', 'ASC');
 
        $query->joinSub($diffPrice, 'tableB', function ($join) {
            $join->on('tableA.id', '=', 'tableB.pro_id');
        })
            ->select('tableA.*', 'tableB.start_date', 'tableB.end_date', 'tableB.price')
                ->where(function($sq) use ($postFrom) {
                    $sq->when($postFrom[0]=='0', function ($syq) {
                        $today = Carbon::now()->format('Y-m-d'); 
                        $syq->whereDate('end_date', '>=', $today);
                    }, function ($stq) use ($postFrom) {
                        $stq->whereDate('start_date', '<=', $postFrom[0])
                        ->whereDate('end_date', '>=', $postFrom[0]);
                    });
                })->groupBy('tableA.id')->orderBy('price', $sortDirection);

Armani

5 years ago

Level 17

Something like this should work:

DB::table('tableA')->select('tableA.*', 'tableB.start_date', 'tableB.end_date', 'tableB.price')->leftJoinSub(DB::table('tableB')->select('id', 'start_date', 'end_date', 'pro_id', 'price', DB::raw('DATEDIFF(end_date, now()->format("Y-m-d")) diff'))->groupBy('id')->latest('diff'), 'tableB', function($join){
            $join->on('tableA.id', 'tableB.pro_id');
         })->having('end_date', '=>', now()->format('Y-m-d'))->groupBy('id')->latest('price')->get()

acetas

5 years ago

Level 1

first of all thank you for the answer. I tried this but it gave the following error again. I guess there is something I should include?

"Call to undefined method October\Rain\Database\QueryBuilder::leftJoinSub()"

Armani

5 years ago

Level 17

You have to use this:

use Illuminate\Support\Facades\DB;

Read the document:

https://laravel.com/docs/8.x/queries#subquery-joins

Or you may add the select at the end like this:

DB::table('tableA')->leftJoinSub(DB::table('tableB')->select('id', 'start_date', 'end_date', 'pro_id', 'price', DB::raw('DATEDIFF(end_date, now()->format("Y-m-d")) diff'))->groupBy('id')->latest('diff'), 'tableB', function($join){
            $join->on('tableA.id', 'tableB.pro_id');
         })->select('tableA.*', 'tableB.start_date', 'tableB.end_date', 'tableB.price')->having('end_date', '=>', now()->format('Y-m-d'))->groupBy('id')->latest('price')->get()

1 like

acetas

5 years ago

Best Answer

Level 1

My Laravel version does not support subquery. However, it is solved as follows. Thanks

$query->select('tableA.*', 'tableB.start_date', 'tableB.end_date', 'tableB.price')
         ->join(DB::raw("(SELECT id, start_date, end_date, pro_id, price,
         DATEDIFF(`tableB`.`end_date`, '2021-03-07') diff
         FROM `tableB` GROUP BY id order by diff asc) tableB "), function ($join)
        {
              $join->on('tableA.id', '=', 'tableB.pro_id');
        })
        ->whereDate('tableB.end_date','>=','2021-03-07')
        ->groupBy('tableA.id')->orderBy('price','DESC')->get();

abrada

5 years ago

Level 1

You also can use DB:raw('your sql raw query');

Please or to participate in this conversation.