Level 75
You do have a model setup, right?
Oct 28, 2022
6
Level 1
sub queries and query builder
is there a way to write a sub query and use query builder (needed to add 'if' clauses not included below for simplicity)
Here is the query I am trying to build in query builder:
select license, itemtype, varieties_string, DATE(date), scale_string, packagetags_string, derivedtable.weightvaluesum, derivedtable.plantcountavg, (derivedtable.weightvaluesum/derivedtable.plantcountavg) as answer
from(
select license, itemtype, varieties_string, DATE(date), scale_string, packagetags_string, Sum (weightvalue) AS weightvaluesum, Avg (plantcount) AS plantcountavg
from weightrecords
where farm_string = 'My Produce, LLC'
Group By license, itemtype, varieties_string, DATE(date), scale_string, packagetags_string
order by license, itemtype, varieties_string, DATE(date), scale_string, packagetags_string
) AS derivedtable
Here is the inner query:
$query = weightrecords::query();
$query = $query->selectRaw('license, itemtype, varieties_string, DATE(date), scale_string, packagetags_string, SUM (weightvalue) AS weightvaluesum, AVG (plantcount) AS plantcountavg');
$query = $query->where('farm_string', '=', 'My Produce, LLC');
$query = $query->groupByRaw('license, itemtype, varieties_string, DATE(date), scale_string, packagetags_string');
$query = $query->orderByRaw('license, itemtype, varieties_string, DATE(date), scale_string, packagetags_string');
$query = $query->get();
but how do I build the outer query? It does not seem to map to something like https://laravel.com/docs/9.x/queries#subquery-where-clauses
Level 1
@jlrdw I have a model for weightrecords
Level 1
I was reading this https://stackoverflow.com/questions/24823915/how-to-select-from-subquery-using-laravel-query-builder
and thought I could do something like this but it's not working:
$query = DB::table('weightrecords')->selectRaw('license, itemtype, varieties_string, DATE(date), scale_string, packagetags_string, SUM (weightvalue) AS weightvaluesum, AVG (plantcount) AS plantcountavg');
$query = $query->where('farm_string', '=', 'My Produce, LLC');
$query = $query->groupByRaw('license, itemtype, varieties_string, DATE(date), scale_string, packagetags_string');
$sub = $query->orderByRaw('license, itemtype, varieties_string, DATE(date), scale_string, packagetags_string');
$yield_per_license = DB::query()->from($sub)->selectRaw('license, itemtype, varieties_string, DATE(date), scale_string, packagetags_string, sub.weightvaluesum, sub.plantcountavg, sub.weightvaluesum / sub.plantcountavg AS answer')->get();
I get
ERROR: zero-length delimited identifier at or near \"\"\"\"\nLINE 1: ..._string, DATE(date), scale_string, packagetags_string) as \"\"\n
SQL: select license, itemtype, varieties_string, DATE(date), scale_string, packagetags_string, sub.weightvaluesum, sub.plantcountavg,
sub.weightvaluesum / sub.plantcountavg AS answer
from (select license, itemtype, varieties_string, DATE(date), scale_string, packagetags_string, SUM (weightvalue) AS weightvaluesum,
AVG (plantcount) AS plantcountavg from \"weightrecords\"
where \"farm_string\" = My Produce, LLC
group by license, itemtype, varieties_string, DATE(date), scale_string, packagetags_string
order by license, itemtype, varieties_string, DATE(date), scale_string, packagetags_string) as \"\")"
Level 10
I think the issue is the subquery needs an alias, which sould be the 2nd parameter of the from() method
$yield_per_license = DB::query()->from($sub)
That's why your query ends up being "select... from (select....) as \"\", which is invalid
Level 1
I think this may be it. Testing.
$query = DB::table('weightrecords')->selectRaw('license, itemtype, varieties_string, DATE(date), scale_string, packagetags_string, SUM (weightvalue) AS weightvaluesum, AVG (plantcount) AS plantcountavg');
$query = $query->where('farm_string', '=', 'My Produce, LLC');
$query = $query->groupByRaw('license, itemtype, varieties_string, DATE(date), scale_string, packagetags_string');
$sub = $query->orderByRaw('license, itemtype, varieties_string, DATE(date), scale_string, packagetags_string');
$yield_per_license = DB::query()->from($sub, 'sub')->selectRaw('license, itemtype, varieties_string, DATE(date), scale_string, packagetags_string, sub.weightvaluesum, sub.plantcountavg, sub.weightvaluesum / sub.plantcountavg AS answer')->get();
Level 1
@psrz yep. that fixed it.
Please or to participate in this conversation.