I made this code 1 in code 2
1)//$palpites=DB::select("SELECT * FROM palpite WHERE id_u='$id' order by id_c desc ");
2) $palpites = DB::table('palpite')->where('id_u',$id)->orderby('id_c','desc') ->paginate(3);
How to turn this code below into the same example as 1,2?
$confrontos=DB::select("SELECT * FROM confrontos as c, palpite as p WHERE p.id_u ='$id' AND EXISTS (SELECT * FROM palpite WHERE c.id = p.id_c) order by id_c desc ");
Good morning!
In the clause where a sub-query involves you should use a Clousure (anonymous function) passing the desired information.
Example:
...
->where(function($q) use ($query) {
$query->select(DB::raw('p.*'))
->from('palpite')
->whereRaw('c.id = p.id_c');
}
UPDATE:
After your explanation of what should be returned, my suggestion is to modify your query first, as follows:
select * from conf
inner join pal on pal.id_c = c.id
where pal.id_u = $id;
In this way you get the same results as you want. To perform the pagination of this query in Laravel you should write something like:
$confrontos = DB::table('confrontos')
->select('*')
->join('palpites', 'palpites.id_confronto', '=', 'confronto.id')
->where('palpites.id_usuario', $id)
->paginate(10);