How to return data array with Join in Laravel with the DB class?

Asked

Viewed 402 times

0

I need some help with that SQL of Laravel:

$process = $db->table('processo')->where('nrprocesso',$id)
 ->leftJoin('viatransporte', function($viatransporte){
      $viatransporte->on('processo.idviatransporte', '=', 'viatransporte.idviatransporte')
      ->whereNotNull('processo.idviatransporte'); })
 ->leftJoin('area', function($area){
     $area->on('processo.idarea', '=', 'area.idarea')
      ->whereNotNull('processo.idarea'); })
 ->leftJoin('moeda', function($moeda){
     $moeda->on('processo.idmoedamle', '=', 'moeda.idmoeda')
       ->whereNotNull('processo.idmoedamle'); });

This precise Join returns two records that are in the database, but returns only one record, the first, the others not. How do I return an array with all records?

->leftJoin('processodocinstrucaodespacho', function($processodocinstrucaodespacho){
  $processodocinstrucaodespacho->on('processo.idprocesso',
                  '=', 'processodocinstrucaodespacho.idprocesso')
     ->where('processodocinstrucaodespacho.iddocinstrucaodespacho', '=', 1); })

->select('processo.*', 'area.aparea', 
         'viatransporte.nmviatransporte', 'moeda.sigla',
         'processodocinstrucaodespacho.nrdocinstrucaodespacho')
->get();
mysql laravel query laravel-5 collection

  • Wouldn’t it be better to take this data through the relationships between the entities? It would be something much clearer. Have tried this side?

  • So, this database is remote I am using $db = DB::Connection('pgsql');, I have not created model for these entities. I am working with 2 banks, the mysql location to save some information, and picking others from this remote.

  • 1

    Which is the SQL that should be corresponding, I think I can help you from it, one thing also the drawing of the 3 tables and their keys are very important also for solution.

  • The Join processodocinstruçáodespacho, in the database when I do this same query returns more than 1 record, but here in the code returns only the first one that is there. Thank you very much

  • Not at all @Enorio

  • Does not solve the problem, the error continues

Show 1 more comment

1 answer

-2

$process = $db->table('processo')->where('nrprocesso',$id)
 ->leftJoin('viatransporte', function($viatransporte){
      $viatransporte->on('processo.idviatransporte', '=', 'viatransporte.idviatransporte')
      ->whereNotNull('processo.idviatransporte'); })
 ->leftJoin('area', function($area){
     $area->on('processo.idarea', '=', 'area.idarea')
      ->whereNotNull('processo.idarea'); })
 ->leftJoin('moeda', function($moeda){
     $moeda->on('processo.idmoedamle', '=', 'moeda.idmoeda')
       ->whereNotNull('processo.idmoedamle'); })->get()->toArray();

For more information: https://laravel.com/docs/5.7/collections#method-toarray

Browser other questions tagged mysql laravel query laravel-5 collection

You are not signed in. Login or sign up in order to post.