specific data result from query

Question

I'm having problems to group my sql query by estacion.id,

$result = \DB::table('estacion')
           ->select('estacion.id as id_est','estacion.nombre as nombre_estacion','eq.id as id_equipo',
             'eq.nombre as nombre_equipo','pa.nombre as nombre_parametro') 
           ->join('equipo_estacion as eq_est', 'estacion.id', 'eq_est.estacion_id')
           ->join('equipo as eq', 'eq_est.equipo_id', 'eq.id')
           ->join('equipo_parametro as eq_param','eq.id','eq_param.equipo_id')
           ->join('parametro as pa','eq_param.parametro_id','pa.id')
           ->get();

and the result of what i want when the estacion is repeated on the image here, so i can loop through the nested arrays

Migrations

   public function up()
  {
    Schema::create('estacion', function (Blueprint $table) {
        $table->increments('id');
        $table->string('nombre');
        $table->integer('xcoord');
        $table->integer('ycoord');
        $table->date('inicio_actividad')->nullable();
        $table->date('fin_actividad')->nullable();
        $table->string('url');
        $table->integer('comuna_id')->unsigned()->nullable();
        $table->foreign('comuna_id')->references('id')->on('comuna');
        $table->integer('region_id')->unsigned()->nullable();
        $table->foreign('region_id')->references('id')->on('region');

        $table->timestamps();
    });
}

   public function up()
{
    Schema::create('equipo_estacion', function (Blueprint $table) {
        $table->increments('id');
        $table->integer('equipo_id')->unsigned();
        $table->foreign('equipo_id')->references('id')->on('equipo')->onDelete('cascade');
        $table->integer('estacion_id')->unsigned();
        $table->foreign('estacion_id')->references('id')->on('estacion')->onDelete('cascade');
        $table->timestamps();
    });
}

 public function up()
{
    Schema::create('equipo', function (Blueprint $table) {
        $table->increments('id');
        $table->string('nombre');
        $table->text('descripcion')->nullable();
        $table->string('marca');
        $table->string('modelo');
        $table->string('nserie')->nullable();
        $table->timestamps();
    });
}


public function up()
{
    Schema::create('equipo_parametro', function (Blueprint $table) {
        $table->increments('id');
        $table->integer('equipo_id')->unsigned();
        $table->foreign('equipo_id')->references('id')->on('equipo')->onDelete('cascade');
        $table->integer('parametro_id')->unsigned();
        $table->foreign('parametro_id')->references('id')->on('parametro')->onDelete('cascade');
        $table->timestamps();
    });
}

  public function up()
{
    Schema::create('parametro', function (Blueprint $table) {
        $table->increments('id');
        $table->string('nombre');
        $table->timestamps();
    });
}

Expected table results

  (Title)Region   Station       MP10     MP2.5-     SO2 -    N02  -   CO   
  ----------------------------------------------------------------------
(row1)Region1  Antofagasta    YES        NO       NO       YES      NO 
  Region 2     Arica           No       yes       no       no       no


#table: "estacion"
  +sortable: array:8 [▶]
  #fillable: array:5 [▶]
  #connection: null
  #primaryKey: "id"
  #keyType: "int"
  +incrementing: true
  #with: []
  #perPage: 15
  +exists: true
  +wasRecentlyCreated: false
  #attributes: array:14 [▶]
  #original: array:14 [▼
    "id" => 7
    "nombre" => "Antofagasta"
    "xcoord" => 1414
    "ycoord" => 1717
    "inicio_actividad" => null
    "fin_actividad" => null
    "url" => "1493682540.jpg"
    "comuna_id" => 12
    "region_id" => 3
    "red_id" => 1
    "huso_id" => 1
    "datum_id" => 2
    "created_at" => "2017-05-01 23:47:41"
    "updated_at" => "2017-05-01 23:49:00"
  ]
  #casts: []
  #dates: []
  #dateFormat: null
  #appends: []
  #events: []
  #observables: []
  #relations: array:1 [▼
    "equipos" => Collection {#363 ▼
      #items: array:2 [▼
        0 => Equipo {#373 ▼
          #table: "equipo"
          +sortable: array:7 [▶]
          #fillable: array:17 [▶]
          #connection: null
          #primaryKey: "id"
          #keyType: "int"
          +incrementing: true
          #with: []
          #perPage: 15
          +exists: true
          +wasRecentlyCreated: false
          #attributes: array:19 [▶]
          #original: array:23 [▶]
          #casts: []
          #dates: []
          #dateFormat: null
          #appends: []
          #events: []
          #observables: []
          #relations: array:2 [▼
            "pivot" => Pivot {#377 ▶}
            "parametros" => Collection {#385 ▶}
          ]
          #touches: []
          +timestamps: true
          #hidden: []
          #visible: []
          #guarded: array:1 [▶]
        }
        1 => Equipo {#382 ▼
          #table: "equipo"
          +sortable: array:7 [▶]
          #fillable: array:17 [▶]
          #connection: null
          #primaryKey: "id"
          #keyType: "int"
          +incrementing: true
          #with: []
          #perPage: 15
          +exists: true
          +wasRecentlyCreated: false
          #attributes: array:19 [▶]
          #original: array:23 [▶]
          #casts: []
          #dates: []
          #dateFormat: null
          #appends: []
          #events: []
          #observables: []
          #relations: array:2 [▼
            "pivot" => Pivot {#376 ▶}
            "parametros" => Collection {#386 ▶}
          ]
          #touches: []
          +timestamps: true
          #hidden: []
          #visible: []
          #guarded: array:1 [▶]
        }
      ]
    }

Looping in view ( this was very close to what i expected, but if one station had several parameters , the second,third etc parameters never matched the column name.

  @foreach($result as $estacion)
           <tr> 
            <td> region </td>
            <td>{{ $item1->nombre  }} </td>

           @foreach($estacion->equipos as $equipo)

            @php($i = 0) 
             @foreach($equipo->parametros as $param)


              <!--Aca comprueba en que posicion dentro del arreglo esta el parametro -->
              @php ($posicion = array_search($param->nombre, $param_header))
                 {{ 'Las posicion es : ->' .$posicion }}</br> 

                  @while ($i < 7 )
                     {{  'valor de i' . $i}} </br>
                    @if($i == $posicion)

                        <td>{{ $param->nombre }} </td>

                          {{ Session::put('break', $i) }}
                           @break
                      @else
                          <td> no </td>
                    @endif   

                  @php ( $i++ )     

                  @endwhile 
                 <!--  @php ( $i++ )    -->

                 {{ Session::flush() }}   
              @endforeach

           @endforeach
          </tr>  
         @endforeach

result image i get when using eloquent here, notice NO2 is 1 position to the right of the corresponding column.

I did use eloquent earlier, but the problem I faced was 3 foreachs to get to the parameters asociated to an equipment that belong to an station, more about it here http://stackoverflow.com/questions/43791227/group-by-stations-in-laravel — Rodrigo Rose, May 08 '17 at 03:09
I edited migrations and results above, MP10 ,MP2.5 etc are parameters, i compare positions against another array with all parameters in order, so if database retrieved row has an equimenent with parameter mp10 i display Yes in the corresponding column, the problem is that some stations can have many equipments and each equiment has 1 parameter, so i need to display it in the station row only below the column — Rodrigo Rose, May 08 '17 at 03:33

Demonyowh · Answer 1 · 2017-05-08T03:55:58.963

0

Use Eloquent Relationship.. In your Eloquent

estacion

public function equipos(){
    return $this->belongsToMany('App\Equipo', 'equipo_estacion', 'estacion_id', 'equipo_id');
}

equipo

public function parametros(){
    return $this->belongsToMany('App\Parametro', 'equipo_parametro', 'equipo_id', 'parametro_id');
}

Then your query should be like this

$result = Estacion::all()->with('equipos.parametros')->get();

Then on your loop

foreach ($result as $estacion) {
   echo $estacion->equipos->first()->parametros->first()->nombre;
}

edited May 08 '17 at 03:55

answered May 08 '17 at 03:33

Demonyowh

1,673
1
9
14

$result = Estacion::with('equipos.parametros')->get(); this is the one that works ,collection above – Rodrigo Rose May 08 '17 at 03:51
no , it didn't work Trying to get property of non-object (View: C:\wamp\www\bitacolar\resources\views\reportes\equipos_estacion.blade.php), i edited above what almost worked for me using eloquent, – Rodrigo Rose May 08 '17 at 04:03
did you use the `php artisan make:model:estacion --migration` to create your model and migration ? as well as the other models ? – Demonyowh May 08 '17 at 04:07
yes, i did, all relationships are ok, i get all the correct data on collection – Rodrigo Rose May 08 '17 at 04:10
if you see the collection above there are two equipments each one have 1 parameter, it need two loops inside the nested foreach to get param->name to get their position, so how do i show it on the first row, since it belong to Antofagasta , i could have 3 ,4 ,5 parameters and have to mention whether or not it exist below the respective parameter columns names – Rodrigo Rose May 08 '17 at 04:15
$param_header = array('MP10','MP2.5','SO2','NO2','CO','O3','BC'); is the array against i compare positions to put the retrieved param below the colum – Rodrigo Rose May 08 '17 at 04:18
well you could not do it in eloquent unless you manually restructure it before passing it to the view .. but doing it without using Eloquent ORM could do .. but not a good practice though .. – Demonyowh May 08 '17 at 04:29
also you could use `@while ($i < count($param_header) )` so it could be dynamic – Demonyowh May 08 '17 at 04:31
I placed an image of results I got using eloquent at the end of edit, when it finds a parameter in the position it displays it and uses break to leave the while loop. the second or third etc param never match the column – Rodrigo Rose May 08 '17 at 12:44

specific data result from query

1 Answers1