Paginate Doubts Arable

Question

I turned that code 1 into 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 1.2 example?

$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 ");

Answer 1

Good morning!

In the clause where it involves a sub-query you must 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 first you modify your query, as follows:

select * from conf 
inner join pal on pal.id_c = c.id 
where pal.id_u = $id;

This way you get the same results you want. To make the pagination of this query in Laravel you must write something like:

$confrontos = DB::table('confrontos') 
->select('*') 
->join('palpites', 'palpites.id_confronto', '=', 'confronto.id') 
->where('palpites.id_usuario', $id) 
->paginate(10);