Laravel and MySQL JOIN

Question

i have a problem with this query... i need insert 2 more joins..

->join('torneo as t','t.idtorneo','=','p.id_torneo')

and

->join('temporada as s','s.idtemporada','=','p.id_temporada')

to run:

->select('p.idpartido','p.id_temporada','t.nombre_torneo as torneo','s.temporada as nombre_temporada','c.nombre as team1','k.nombre as team2','p.estado')

And i have this code working... but when add the other joins.. it fail.

"SQLSTATE[23000]: Integrity constraint violation: 1052"

public function index(Request $request)
    {
    	if ($request)
    	{
    		$query=trim($request->get('searchText'));
    		$partidos=DB::table('partido as p')
    		->join('club as c','p.idteam1','=','c.idclub')
            ->join('club as k','p.idteam2','=','k.idclub')
            ->select('p.idpartido','p.id_temporada','c.nombre as team1','k.nombre as team2','p.estado')
    		->where('estado','=','Activo')
    		->orderBy('idpartido','desc')
    		->paginate(7);
    		return view('partidos.index',["partidos"=>$partidos,"searchText"=>$query]);
    	}
    }

Sorry for my bad english.

Answer 1

There might be a problem with the data as you are using ->join which is innner join in Mysql try to use ->leftJoin which wont let the query break if the data is available it will make a join else it will leave the selected column from joined table as blank hope it helps :)