I am making an api with laravel (for learning purposes), I have two tables in my database, one is Avisos
and the other is called Valoraciones
, notices contains, as its name says, notices that can be commented, that is, have several comments (ratings = comments ) table ratio is 1(warning) to many(comments)
What I want is to fetch all the comments that a post contains as follows:
Aviso{//esto es a modo de ejemplo
idAviso:1,
tituloAviso:"Ejemplo de titulo",
descripcionAviso:"Ejemplo de descrpoicion",
valoraciones:[
{
idValoracion:1,
DespcripcionValoracion:"ejemplo de descripcion"
},
{
idValoracion:2,
DespcripcionValoracion:"ejemplo de descripcion2sada"
}
]
}
What I have tried so far is to use a left join
but they are not the expected results, as I show below
[
{
"id": 60,
"titulo": "Ejemplo1",
"descripcion": "Ejemplo1",
"imagen": "ErrorIngresoHosp.jpg",
"fecha": "2022-06-02 18:59:05",
"name": "David Garcia",
"comentario": "Ejemplo de comentario.."
},
{
"id": 60,
"titulo": "Ejemplo1",
"descripcion": "Ejemplo1",
"imagen": "ErrorIngresoHosp.jpg",
"fecha": "2022-06-02 18:59:05",
"name": "David Garcia",
"comentario": "exste es otro ejemplo de comentario"
},
{
"id": 61,
"titulo": "Ejemplo2",
"descripcion": "Ejemplo2",
"imagen": "ErrorIngresoHosp.jpg",
"fecha": "2022-06-02 18:59:13",
"name": "David Garcia",
"comentario": null
},
{
"id": 62,
"titulo": "Ejemplo3",
"descripcion": "Ejemplo3",
"imagen": "Hacker-Anonymous-wants-you.png",
"fecha": "2022-06-03 15:21:56",
"name": "David Garcia",
"comentario": null
}
]
As you can see, each publication that has a comment brings it twice , that's why the publication with id 60 is twice because it has been commented twice, currently my query is like this:
public function index()
{
//Listamos todos los avisos
$avisos = new Avisos();
$avisos = Avisos::select(
'Avisos.id',
'Avisos.titulo',
'Avisos.descripcion',
'Avisos.imagen',
'Avisos.created_at as fecha',
'users.name',
'valoraciones.comentario'
)->join('categorias','categorias.id', '=','avisos.idCategoria')
->join('estados','estados.id', '=','avisos.idEstado')
->join('users','users.id', '=','avisos.idUsuario')
->leftjoin('valoraciones','valoraciones.idAviso', '=','avisos.id')
->get();
return response()->json([
"Avisos" => $avisos
], Response::HTTP_OK);
}
If the developer community can help me I would appreciate it, or guide me how it could be better, in case I need to use subqueries
You can use Eloquent to make it easy to list relationships.
In your ads model (app/Models/Aviso.php if you are using php >= 8.0) create the relationship with ratings:
When consulting the notices, they can do so with:
This should add your ratings to each ad.