0

I have the following request 

$latestTrailers =  DB::table('game_trailer')
                        ->join('trailers', 'trailers.id', '=', 'game_trailer.trailer_id')
                        ->join('games', 'games.id', '=', 'game_trailer.game_id')
                        ->join('device', 'device.id', '=', 'games.device_id')
                        ->where('game_trailer.bool', 1)
                        ->orderBy('game_trailer.created_at', 'desc')
                        ->select('name', 'trailer_link', 'device_url', 'game_trailer.created_at')
                        ->limit(3)
                        ->get();

And the following result 

    Collection {#407 ▼
  #items: array:3 [▼
    0 => {#420 ▼
      +"name": "Persona 5"
      +"trailer_link": "cc-ClutaN_I"
      +"device_url": "ps4"
      +"created_at": "2018-02-13 16:32:34"
    }
    1 => {#395 ▼
      +"name": "Persona 5"
      +"trailer_link": "cc-ClutaN_I"
      +"device_url": "ps3"
      +"created_at": "2018-02-13 16:30:36"
    }
    2 => {#427 ▼
      +"name": "Halo 3: ODST"
      +"trailer_link": "Zz6FNKawJBc"
      +"device_url": "microsoft-xbox-360"
      +"created_at": "2018-02-13 16:27:56"
    }
  ]
}

I'd like the "trailer_link" value to be unique. And so make the request select the next entrie in the database if this column is not unique.

I tried the distinct() method but it doesn't work as I only want to use it on only one value.

Are there any equivalents ? Or another way to do it ?

M.Pau
  • 147
  • 1
  • 3
  • 12
  • 2
    try `groupBy('trailer_link')` – Sohel0415 Feb 13 '18 at 19:03
  • I tried this one and got the following error that I wasn't able to resolve either `Illuminate \ Database \ QueryException (42000) SQLSTATE[42000]: Syntax error or access violation: 1055 Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column ` – M.Pau Feb 13 '18 at 19:08
  • 1
    seems strict db problem, you need to disable strict mode, in your config/database `'strict' => false,` – Sohel0415 Feb 13 '18 at 19:14
  • Thank you, that works for the `groupBy('trailer_link')`, but now the `orderBy` doesn't work, it only select the 3 first entries. – M.Pau Feb 13 '18 at 19:32
  • in your code you have , limit(3) , to only select the 3 first entries – Unai Susperregi Feb 13 '18 at 20:00
  • My bad I just missread the data, the limit(3) is fine, everything works, thank everyone. – M.Pau Feb 13 '18 at 20:40

0 Answers0