Be part of JetBrains PHPverse 2026 on June 9 – a free online event bringing PHP devs worldwide together.

birdietorerik's avatar

What is wrong with this query ?

Hi!

Have this function in laravel-model

 public static function getgpsdta($request,$golfID)
    {
        $flagget=$request->flagg;
        $green=$request->green;
        $orange=$request->orange;
        $red=$request->red;
        $judge=$request->judge;
        $toilet=$request->toilet;
        $holenumber=$request->holenumber;
        $dl=$request->dl;
        $bm=$request->bm;
        $td=$request->td;
        $range=$request->range;
        $tl=$request->tl;

        $query = DB::table('gpstrackers')
        ->select('*')
        ->whereIn('id', function($query) {
            $query->selectRaw('MAX(id) as id')
                ->from('gpstrackers')
                ->where('golfclub_id', $golfID)
                ->groupBy('flight');
        });
        
        if($holenumber=='true'){       
            $query1 = DB::table('holesclub');
            $query->union($query1);
        }

        if($flagget=='true'){       
            $query2 = DB::table('flagpositions');
            $query->union($query2);
        }

        if($toilet=='true'){       
            $query3 = DB::table('objectsclub');
            $query->union($query3);
        }
        
        if($flagget=='false')
        {           
            $query->where('type', '<>', 2);
        } 

        if($judge=='false')
        {           
            $query->where('type', '<>', 3);
        } 

        if($toilet=='false')
        {           
            $query->where('type', '<>', 5);
        } 

        if($holenumber=='false')
        {           
            $query->where('type', '<>', 7);
        } 

        if($dl=='false')
        {           
            $query->where('type', '<>', 12);
        } 

        if($bm=='false')
        {           
            $query->where('type', '<>', 8);
        } 

        if($td=='false')
        {           
            $query->where('type', '<>', 9);
        } 

        if($tl=='false')
        {           
            $query->where('type', '<>', 10);
        } 

        if($range=='false')
        {           
            $query->where('type', '<>', 11);
        } 

        if($green=='false'){
            $query->where('colormarker', '<>', 1);
        }

        if($orange=='false'){
            $query->where('colormarker', '<>', 2);
        }

        if($red=='false'){
            $query->where('colormarker', '<>', 3);
        }
        
        $gpsdata = $query->get();

        return $gpsdata;
        
    }

Gives me this error:

"message": "SQLSTATE[21000]: Cardinality violation: 1222 The used SELECT statements have a different number of columns (SQL: (select * from `gpstrackers` where `id` in (select MAX(id) as id from `gpstrackers` group by `flight`, `player_id_ref`) and `golfclub_id` = 7 and `type` <> 2 and `type` <> 12 and `colormarker` <> 1) union (select * from `holesclub`) union (select * from `objectsclub`))",

Cang figer out what is wrong ?

0 likes
4 replies
s4muel's avatar
s4muel
Best Answer
Level 50

All queries "unioned" by UNION should return the same number of columns. i suppose the tables gpstrackers, holesclub and objectsclub dont have exactly same columns

Snapey's avatar

Sounds like you are trying to union multiple queries which independently contain a different number of columns.

Why are you using union on seemingly totally different types of data

Sinnbeck's avatar

You also forgot a use statement here

$query = DB::table('gpstrackers')
        ->select('*')
        ->whereIn('id', function($query) use ($golfID) { //here
            $query->selectRaw('MAX(id) as id')
                ->from('gpstrackers')
                ->where('golfclub_id', $golfID)
                ->groupBy('flight');
        });

Please or to participate in this conversation.