In my app, I have a few models with some relations to each one. My problem is that I must get all related data, so I am using the querybuilder, and I don't know where to begin.
If anyone knows and wants to help me, these are my models and relations.(Down I will explain how I want the data).
Fabricacion:(product)
<?php
namespace App\Models;
use Illuminate\Database\Eloquent\Model;
class Fabricacion extends Model
{
protected $table = "fabricacion";
protected $primaryKey = "id_fabricacion";
protected $fillable = ["id_order", "id_megacart", "reference", "name", "width", "height", "length", "date_update", "id_categoria", "id_ubicacion", "id_incidencia", "estado"];
public $timestamps = true;
public function incidencia() {
return $this->belongsTo("App\Models\Incidencia", "id_incidencia", "id_incidencia");
}
public function categoria() {
return $this->belongsTo("App\Models\Categoria", "id_categoria", "id_categoria");
}
public function ubicacion() {
return $this->belongsTo("App\Models\Ubicacion", "id_ubicacion", "id_ubicacion");
}
public function medidas() {
return $this->belongsToMany("App\Models\Medida", "fabricacion_medidas", "id_fabricacion", "id_medida")->withPivot("medida");
}
public function atributos() {
return $this->belongsToMany("App\Models\Atributo", "fabricacion_atributos", "id_fabricacion", "id_atributo")->withPivot("atributo");
}
}
Categoria:(category)
<?php
namespace App\Models;
use Illuminate\Database\Eloquent\Model;
class Categoria extends Model
{
protected $table = "categorias";
protected $primaryKey = "id_categoria";
protected $fillable = ["id_prestashop", "nom_categoria"];
public $timestamps = false;
public function fabricaciones() {
return $this->hasMany("App\Models\Fabricacion", "id_categoria", "id_categoria");
}
public function tareas() {
return $this->belongsToMany("App\Models\Tarea", "categorias_tareas", "id_categoria", "id_tarea")->withPivot("orden");
}
}
Ubicacion:(ubication)
<?php
namespace App\Models;
use Illuminate\Database\Eloquent\Model;
class Ubicacion extends Model
{
protected $table = "ubicaciones";
protected $primaryKey = "id_ubicacion";
protected $fillable = ["nom_ubicacion"];
public $timestamps = false;
public function fabricaciones() {
return $this->hasMany("App\Models\Fabricacion", "id_incidencia", "id_incidencia");
}
}
Medida:(measure)
<?php
namespace App\Models;
use Illuminate\Database\Eloquent\Model;
class Medida extends Model
{
protected $table = "medidas";
protected $primaryKey = "id_medida";
protected $fillable = ["nom_medida"];
public $timestamps = false;
public function fabricaciones() {
return $this->belongsToMany("App\Models\Fabricacion", "fabricacion_medidas", "id_medida", "id_fabricacion")->withPivot("medida");
}
}
Atributo:(attribute)
<?php
namespace App\Models;
use Illuminate\Database\Eloquent\Model;
class Atributo extends Model
{
protected $table = "atributos";
protected $primaryKey = "id_atributo";
protected $fillable = ["id_prestashop", "nom_medida"];
public $timestamps = false;
public function fabricaciones() {
return $this->belongsToMany("App\Models\Fabricaion", "fabricacion_atributos", "id_atributo", "id_fabricacion")->withPivot("atributo");
}
}
-- Categoria Relation:(category)
<?php
namespace App\Models;
use Illuminate\Database\Eloquent\Model;
class Tarea extends Model
{
protected $table = "tareas";
protected $primaryKey = "id_tarea";
protected $fillable = ["nom_tarea", "posicion"];
public $timestamps = false;
public function categorias() {
return $this->belongsToMany("App\Model\Categoria", "categorias_tareas", "id_tarea", "id_categoria")->withPivot("orden");
}
public function zonas() {
return $this->hasMany("App\Models\Zona", "id_tarea", "id_tarea");
}
}
-- Tarea Relation:(task)
<?php
namespace App\Models;
use Illuminate\Database\Eloquent\Model;
class Zona extends Model
{
protected $table = "zonas";
protected $primaryKey = "id_zona";
protected $fillable = ["nom_zona", "posicion", "id_tarea", "impresora", "minSierra"];
public $timestamps = false;
public function tarea() {
return $this->belongsTo("App\Models\Tarea", "id_tarea", "id_tarea");
}
}
The problem is that I want to get all the fabricacion(product) data filtering by categoria(category), ubicacion(ubication), zona(workspace) and tarea(task), getting it's atributos(attributes) and medidas(measure).
For example: I want to get all fabricacion(product) data from the zona(workspace) => X, where categoria(category) => Y and ubicacion(ubication) => A. The products have a category, and the category is related with a task(so, the products with X category will be in workspace Y). And the products have an state(so, if is in sate 1 and in category the 1 is a task, the product will be in that task).
This might be badly explained. Sometimes I dont explain myself, and I don't know a lot of english.
Well, finally a got this, thanks for the responses anyway ^^
$productos = Fabricacion::where(
$where
)
->whereHas("categoria", function($query) use($id_zona) {
$query->whereHas("tareas", function($query) use($id_zona) {
$query->whereHas("zonas", function($query) use($id_zona) {
$query->where([
["zonas.id_zona", "=", $id_zona]
]);
$query->whereRaw("categorias_tareas.orden = `fabricacion`.`estado`");
});
});
})
->with(["medidas", "atributos"])
->orderBy("fabricacion.date_update", $date);
In this case, I would create a controller method that receives all the filters (zona, categoria and ubicacion) from the views, sent through ajax. After that, you can use the parameters in your query and have a generic function that applies the filters and returns what you specified in the views.
So, the steps would be:
Create a route that calls the controller function.
Send an ajax request to that route with the filters you want to apply in the data of the message.
Receive the filter's information in the controller function and create the query.
Add a route to your web.php or routes.php file.
Route::get('fabricaciones/data', 'HomeController@data'); // whatever controller name
An example of the controller method to make the query would be something like this:
function data(Request $request)
{
$categoria_id = $request->input('categoria_id');
// ...
$result = DB::table('fabricacion')
->join('categorias', 'categorias.id', '=', 'fabricacion.categoria_id')
->join(...) // more joins with the other tables
->where('categorias.id', '=', $categoria_id)
->where(...) // apply more filters
->select('fabricacion.*')
->get();
return $result;
}
The ajax request in JavaScript (using jQuery) in your View would be something like this, but pointing to your created route:
$.ajax({
url: 'fabricaciones/data',
type: 'GET',
dataType: 'json',
data: { categoria_id: 1 },
})
.done(function(received_data) {
console.log(received_data);
})
.fail(function() {
console.log("error");
});