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.

Are you sure this query is causing the error? That would be strange for a SELECT statement. — Paul Spiegel
– Paul Spiegel, Commented May 31, 2018 at 13:15
Hi, integrity constraint might be related with your foreign keys, can you update your question and show us your migration files? — Pol Lluis
– Pol Lluis, Commented May 31, 2018 at 13:37
I dont use migration file... i make the database with phpmyadmin for testing — KatiaSisHost
– KatiaSisHost, Commented May 31, 2018 at 13:50

Sagar Ahuja · Accepted Answer · 2018-05-31 13:17:06Z

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 :)

answered May 31, 2018 at 13:17

Sagar Ahuja

7246 silver badges23 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

KatiaSisHost Over a year ago

i change join for leftjoin and add ->leftjoin('torneo as t','p.id_torneo','=','t.idtorneo') and 't.nombre_torneo as torneo_nombre' in select and... error: Error: SQLSTATE[23000]: Integrity constraint violation: 1052 Column 'estado' in where clause is ambiguous (SQL: select count(*) as aggregate from partido as p left join club as c on p.idteam1 = c.idclub left join club as k on p.idteam2 = k.idclub left join torneo as t on p.id_torneo = t.idtorneo where estado = Activo)

Sagar Ahuja Over a year ago

where clause is ambiguous try to give an alias name to your selected columns for example if I am joining two tables T1 and T2 which have id column as common then if I want to select T1's id column I would do something like select ('T1.id').

Collectives™ on Stack Overflow

Laravel and MySQL JOIN

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related