Select from a Subquery using Query Builder

I have a data table that I want to allow users to sort by whichever column they choose. One of these data tables is supposed to show the 20 most expensive parts in a workorder. Here is the query I'm trying to duplicate using Query Builder:

select PART_NUMBER, DESCRIPTION, UNIT_PRICE
from (
    select top 20 p.ID as PART_NUMBER, p.DESCRIPTION, ps.UNIT_PRICE
	from requirement r 
	join PART p on p.ID=r.PART_ID
	join PART_SITE ps on p.ID=ps.PART_ID
	where r.WORKORDER_BASE_ID=?
	order by ps.UNIT_PRICE desc
)
order by DESCRIPTION desc

How do I select from a subquery?

grozavule

5 years ago

Level 3

I figured it out.

$partsSubquery = DB::connection('vmfg')
                ->table('REQUIREMENT as r')
                ->join('PART as p', 'p.ID', '=', 'r.PART_ID')
                ->join('PART_SITE as ps', 'p.ID', '=', 'ps.PART_ID')
                ->select('p.ID as PART_NUMBER', 'p.DESCRIPTION', 'ps.UNIT_PRICE')
                ->where('r.WORKORDER_BASE_ID', '=', $validInput['workorder_number'])
                ->orderBy('ps.UNIT_PRICE', 'desc')
                ->limit(20);

$partsQuery = DB::connection('vmfg')
                ->query()
                ->fromSub($partsSubquery, 'PE')
                ->select('PART_NUMBER', 'DESCRIPTION', 'UNIT_PRICE');

if(isset($validInput['sortBy']))
            {
                $partsQuery->orderBy($validInput['sortBy'], $validInput['sort']);
            }
            else
            {
                $partsQuery->orderBy('UNIT_PRICE', 'desc');
            }
$partsQuery->get()

I noticed that no one chimed in on this. Am I barking up the wrong tree with Query Builder? Is this better done using Eloquent?

MichalOravec

5 years ago

Best Answer

Level 75

Query builder it better for that, but for orderBy part use conditional clauses

https://laravel.com/docs/6.x/queries#conditional-clauses

$partsQuery = DB::connection('vmfg')
    ->query()->select('PART_NUMBER', 'DESCRIPTION', 'UNIT_PRICE')
    ->fromSub($partsSubquery, 'PE')
    ->when(isset($validInput['sortBy']), function ($query) use ($validInput) {
        return $query->orderBy($validInput['sortBy'], $validInput['sort']);
    }, function ($query) {
        return $query->orderByDesc('UNIT_PRICE');
    })->get();

1 like

Please or to participate in this conversation.