SQLAlchemy Group by query with flask framework and postgresSQL-CodePudding

I am having issue figuring out the right query to get my desire result with sqlalchemy on my Flask application. I need the query to return result as an list of dictionaries as the below code

[{
    "city": "San Francisco",
    "state": "CA",
    "venues": [{
        "id": 1,
        "name": "The Musical Hop",
        "num_upcoming_shows": 0,
    }, {
        "id": 3,
        "name": "Park Square Live Music & Coffee",
        "num_upcoming_shows": 1,
    }]
}, {
    "city": "New York",
    "state": "NY",
    "venues": [{
        "id": 2,
        "name": "The Dueling Pianos Bar",
        "num_upcoming_shows": 0,
    }]
}]

I am trying to return the list of venues on the database, but now group by their state and city and join to the shows table to get the number of up coming shows for that particular venue as show in the list above.

To explain the list of dictionaries above the, the query is returning 2 columns from the venues table which are state and city and then an alias call venues which also returns a list of dictionaries containing the venues names that are in that state and city and also a count of upcoming show for each venue base on the relationship with the shows table.

I have 3 model tables venues, artists and shows, with a many to many relationship between then

class Show(db.Model):
 __tablename__ = "shows"
 id = db.Column(db.Integer, primary_key=True)
 artist_id = db.Column(db.Integer, db.ForeignKey('artists.id'))
 venue_id = db.Column(db.Integer, db.ForeignKey('venues.id'))
 start_time = db.Column(db.DateTime(timezone=True))
 artist = db.relationship("Artist", backref=db.backref("shows", cascade="all, delete-orphan"))
 venue = db.relationship("Venue", backref=db.backref("shows", cascade="all, delete-orphan"))



class Artist(db.Model):
  __tablename__ = 'artists'

id = db.Column(db.Integer, primary_key=True)
name = db.Column(db.String(), nullable=False)
description = db.Column(db.String(), nullable=False)
city = db.Column(db.String(120), nullable=False)
state = db.Column(db.String(120), nullable=False)
phone = db.Column(db.String(120), nullable=False)
genres = db.Column(db.String(), nullable=False)
image_link = db.Column(db.String(500), nullable=False)
facebook_link = db.Column(db.String(120), nullable=False)
website_link = db.Column(db.String(300), nullable=False)
looking_for_venue = db.Column(db.Boolean, nullable=False, default=False)

class Venue(db.Model):
  __tablename__ = 'venues'

 id = db.Column(db.Integer, primary_key=True)
 name = db.Column(db.String(), nullable=False)
 description = db.Column(db.String(), nullable=True)
 genres = db.Column(db.String(), nullable=False)
 city = db.Column(db.String(120), nullable=False)
 state = db.Column(db.String(120), nullable=False)
 address = db.Column(db.String(300), nullable=False)
 phone = db.Column(db.String(120), nullable=True, unique=True)
 image_link = db.Column(db.String(500), nullable=True)
 facebook_link = db.Column(db.String(300), nullable=False)
 website_link = db.Column(db.String(300), nullable=True)
 looking_for_talent = db.Column(db.Boolean, nullable=False, default=False)

I would really appreciate if anyone can help me with the SQL query and also the SQLAlchemy query

CodePudding user response：

SQL query:

select v.city, v.state, v.name, v.id, count(v.id)
from show s
join venue v on v.id = s.venue_id
group by v.city, c.state, v.name, v.id

SQLAlchemy query equivalent:

query = Show.query.join(Venue)
.with_entities(Venue.city, Venue.state, Venue.name, Venue.id, func.count(Venue.id))
.group_by(Venue.city, Venue.state, Venue.name, Venue.id)

It would be only one query. So in my opinion grouping can be done at application site:

results = {}
for city, state, name, id, show_count in query:
    location = (city, state)
    if location not in results:
        results[location] = []
    results[location].append({"id": id, "name": name, "num_upcoming_shows": show_count})
output = [
   {"city": key[0], "state": key[1], "venues": value for key, value in results.items()
]