Laravel query builder: create subquery, subquery has too many columns

0
votes

I am trying to join two tables (visits and observations) and extract two columns (a year+month combination and the count total). I am working in php+Laravel 7.3 and I am trying to use their query builder. Somehow I keep getting the error: 'subquery has too many columns'.

In postgres the query would like this: select to_char(visits.startdate, 'YYYY-MM') as yearmonth, SUM(observations.number) as total from observations join visits on visits.id=observations.id where visit_id in (1,2,3,4,5) and species_id = 13 group by (yearmonth)

I have tried this:

$allVisObs = DB::table('visits')
        ->join('observations', 'visits.id', '=', 'observations.visit_id')
        ->select(DB::raw("to_char(visits.startdate, 'YYYY-MM') as yearmonth") , DB::raw("SUM(observations.number)"))
        ->whereIn('visits.id', $allVisits)
        ->where('observations.species_id', $spid)
        ->groupBy('yearmonth')->get();

And also this:

$visObs = DB::table('visits')
        ->join('observations', 'visits.id', '=', 'observations.visit_id')
        ->select(DB::raw("to_char(visits.startdate, 'YYYY-MM') as yearmonth") , DB::raw("SUM(observations.number)"))
        ->whereIn('visits.id', $allVisits) 
        ->where(function ($query) use ($spid) {
           $query->where('observations.species_id', '=', $spid);
        })
        ->groupBy('yearmonth')->get();

But both give me the error: 'subquery has too many columns'. I checked that $allVisits is a valid list of ids, and $spid is also a correct id. The query runs fine without the line: "->where('observations.species_id, $spid)". I therefore suspect Laravel somehow makes a subquery from the wherein, and tries to add the other where and fails. (I tried switching the wheres around, but that didn't work). I can ofcourse use BD::raw for the entire query, but I would like to stick with the query builder as much as possible. Any help would be greatly appreciated.

2
laravelpostgresqlsubquery

2 Answers

0
votes

Could you try by using just one DB::raw?

->select(DB::raw("to_char(visits.startdate, 'YYYY-MM') as yearmonth, SUM(observations.number)"))
0
votes

Thanks for your reply, I tried but combining everything in one DB:raw does not solve the issue. However, placing the wherein subquery inside custom function does seems to work:

$visObs = DB::table('visits')
        ->join('observations', 'visits.id', '=', 'observations.visit_id')
        ->select(DB::raw("to_char(visits.startdate, 'YYYY-MM') as yearmonth, SUM(observations.number)"))
        ->where('observations.species_id', '=', $spid) //I moved this out of the function
        ->where(function ($query) use ($visitIds) {
           $query->whereIn('visits.id', $visitIds); //and I put this in this function
        })
        ->groupBy('yearmonth')->get();