Query with Join that works in Phpmyadmin does not work in Laravel

2

I have problems passing a traditional query to laravel. It works on phpmyadmin, but when I pass it to Laravel, it returns 0 records.

TALUMNOS TABLE

provincia (int)
dni (int)
nombre (varchar)

TESCUELAS TABLE:

 provincia (int)
 dni (int)
 anio (int)

OK works on phpmyadmin:

select DISTINCT dni,nombre from talumnos inner join tescuelas on
tescuelas.provincia=talumnos.provincia where tescuelas.provincia=18
and tescuelas.anio<>0 AND tescuelas.dni=talumnos.dni   

but in Laravel it does not work (query1):

DB::table('talumnos')
->join('tescuelas', 'tescuelas.provincia','=','talumnos.provincia')
->select('dni','nombre')  
->where('tescuelas.provincia','=',18) 
->where('tescuelas.anio','<>',0) 
->where('tescuelas.dni','=','talumnos.dni') 
->distinct()
->get(); 

this either, with double join (query 2):

DB::table('talumnos')
->join('tescuelas', function ($join) {
   $join->on('tescuelas.provincia','=','talumnos.provincia');
   ->orOn('tescuelas.dni','=','talumnos.dni');
   })
->select('tau_nmarc','gru_cgrup','gru_ngrup')
->where('tescuelas.provincia','=',18) 
->where('tescuelas.anio','<>',0) 
->where('tescuelas.dni','=','talumnos.dni') 
->distinct()
->get(); 

My approach is in this part where I understand this is the problem:

->where('tescuelas.dni','=','talumnos.dni') 

, but I already tried variations doing two joins (like the 2 code that I put), which still does not work.

    
asked by rendor9 29.07.2018 в 01:00
source

1 answer

2

Finally I stay like this, working:

DB::table('talumnos')
->join('tescuelas', 'tescuelas.provincia','=','talumnos.provincia')
->select('dni','nombre')  
->where('tescuelas.provincia','=',18) 
->where('tescuelas.anio','<>',0) 
->whereColumn('tescuelas.dni','talumnos.dni') 
->distinct()
->get(); 
    
answered by 29.07.2018 в 02:21