How can I write this sql query in Laravel?

http://sqlfiddle.com/#!9/1249d9/30

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);

The reason why it throws this error is because you might be using an older version of Laravel. As far as I remember JoinSub is available from 5.6.

rebaz88.software57521 said:

The reason why it throws this error is because you might be using an older version of Laravel. As far as I remember JoinSub is available from 5.6.

So how can I do this query as an alternative?

If you're starting with the SQL query, use Db::select for things like this...

$resultArray = Db::select("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");

Pass params like this

Db::select('select * from sometable where name = :name', ['name' => 'Charles']);

Check out the documentation on Running Raw SQL queries for more details: https://octobercms.com/docs/database/basics#running-queries

Last updated 4 years ago

daftspunk said:

If you're starting with the SQL query, use Db::select for things like this...

$resultArray = Db::select("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");

Pass params like this

Db::select('select * from sometable where name = :name', ['name' => 'Charles']);

Check out the documentation on Running Raw SQL queries for more details: https://octobercms.com/docs/database/basics#running-queries

yes, it gives the output I want, but since I filter with different variables over $query, I have to return it.

Can I use $query in this format?

foreach ($sort as $_sort){
    if(in_array($_sort, array_keys(self::$allowedSortingOptions))){
        $parts = explode(' ', $_sort);

        if(count($parts) < 2){
            array_push($parts, 'desc');
        }

        list($sortField, $sortDirection) = $parts;

        if($sortField == 'price'){

            \DB::select("select tableA.*, tableB.price, tableB.diff from tableA right join(SELECT id, end_date, pro_id, price, DATEDIFF(tableB.end_date, '2021-03-08') diff FROM tableB GROUP BY id order by diff asc) tableB on tableA.id = tableB.pro_id where (date(end_date) >= '2021-03-08') group by tableA.id order by price desc");

        }else{
            $query->orderBy($sortField, $sortDirection);
        }

    }
}

$lastPage = $query->paginate($perPage, $page)->lastPage();

if($lastPage < $page){
    $page = 1;
}

return $query->paginate($perPage, $page);