Currently, I am utilizing knex to craft queries for my express server. Within my database, I have two tables structured as follows:
My objective is to establish a join between the two tables using the "owner_id" foreign key. Subsequently, I aim to retrieve objects that include an "avg" key, indicating the average of all ratings by the respective owner, and an "owner_name" key denoting the name of the restaurant owner. Additionally, I need to organize the results by the owner's name.
This is the snippet I have incorporated into my knex query up to this point:
function listAverageRatingByOwner() {
return knex("restaurants as r")
.join("owners as o", "r.owner_id", "o.owner_id")
.select("r.rating as avg", "o.owner_name")
.orderBy("o.owner_name")
}
I have also experimented with the following approach:
return knex("restaurants as r")
.join("owners as o" , "r.owner_id", "o.owner_id")
.select("r.rating as avg", "o.owner_name")
.groupBy("o.owner_name")
.avg("r.rating")
.orderBy("o.owner_name")
Although this implementation does return data, the owners are not grouped together by their name with the corresponding average rating for each.
This is the current data output I am receiving: enter image description here
However, my anticipated outcome is as follows:
{
"data": [
{
"avg": 3.8200000000000003,
"owner_name": "Amata Frenzel;"
},
{
"avg": 2.25,
"owner_name": "Curtice Grollmann"
},
{
"avg": 2.45,
"owner_name": "Daffy Furzer"
}
]
}I would greatly appreciate any assistance on how to properly group by owner_name along with the average rating for each owner. Thank you!
Below are the migrations for the two tables in my database:
Owners
Restaurants