Tukukkk

I'am using Flask-SQLAlchemy and i use one-to-many relationships. Two models

class Request(db.Model):



      id = db.Column(db.Integer, primary_key = True)

      r_time = db.Column(db.DateTime, index = True, default=datetime.utcnow)

      org = db.Column(db.String(120))

      dest = db.Column(db.String(120))

      buyer_id = db.Column(db.Integer, db.ForeignKey('buyer.id'))

      sale_id = db.Column(db.Integer, db.ForeignKey('sale.id'))

      cost = db.Column(db.Integer)

      sr = db.Column(db.Integer)

      profit = db.Column(db.Integer)



      def __repr__(self):

          return '<Request {} by {}>'.format(self.org, self.buyer_id)



class Buyer(db.Model):

      id  = db.Column(db.Integer, primary_key = True)

      name = db.Column(db.String(120), unique = True)

      email = db.Column(db.String(120), unique = True)

      requests = db.relationship('Request', backref = 'buyer', lazy='dynamic')



      def __repr__(self):

          return '<Buyer {}>'.format(self.name)

I need to identify which Buyer has a minimum requests from all
of the buyers.

I could do it manually by creating additional lists and put all
requests in a lists and search for the list. But I believe there is another simple way to do it via SQLAlchemy query

You can do this with a CTE (common table expression) for a select that produces buyer ids together with their request counts, so

buyer_id | request_count

:------- | :------------

1        | 5

2        | 3

3        | 1

4        | 1

You can filter here on the counts having to be greater than 0 to be listed.

You can then join the buyers table against that to produce:

buyer_id | buyer_name | buyer_email      | request_count

:------- | :--------- | :--------------- | :------------

1        | foo        | foo@example.com  | 5

2        | bar        | bar@example.com  | 3

3        | baz        | baz@example.com  | 1

4        | spam       | spam@example.com | 1

but because we are using a CTE, you can also query the CTE for the lowest count value. In the above example, that's 1, and you can add a WHERE clause to the joined buyer-with-cte-counts query to filter the results down to only rows where the request_count value is equal to that minimum number.

The SQL query for this is

WITH request_counts AS (

    SELECT request.buyer_id AS buyer_id, count(request.id) AS request_count

    FROM request GROUP BY request.buyer_id

    HAVING count(request.id) > ?

)

SELECT buyer.*

FROM buyer

JOIN request_counts ON buyer.id = request_counts.buyer_id

WHERE request_counts.request_count = (

    SELECT min(request_counts.request_count)

    FROM request_counts

)

The WITH request_counts AS (...) defines a CTE, and it is that part that would produce the first table with buyer_id and request_count. The request_count table is then joined with request and the WHERE clause does the filtering on the min(request_counts.request_count) value.

Translating the above to Flask-SQLAlchemy code:

request_count = db.func.count(Request.id).label("request_count")

cte = (

    db.select([Request.buyer_id.label("buyer_id"), request_count])

    .group_by(Request.buyer_id)

    .having(request_count > 0)

    .cte('request_counts')

)

min_request_count = db.select([db.func.min(cte.c.request_count)]).as_scalar()

buyers_with_least_requests = Buyer.query.join(

    cte, Buyer.id == cte.c.buyer_id

).filter(cte.c.request_count == min_request_count).all()

Demo:

>>> __ = db.session.bulk_insert_mappings(

...     Buyer, [{"name": n} for n in ("foo", "bar", "baz", "spam", "no requests")]

... )

>>> buyers = Buyer.query.order_by(Buyer.id).all()

>>> requests = [

...     Request(buyer_id=b.id)

...     for b in [*([buyers[0]] * 3), *([buyers[1]] * 5), *[buyers[2], buyers[3]]]

... ]

>>> __ = db.session.add_all(requests)

>>> request_count = db.func.count(Request.id).label("request_count")

>>> cte = (

...     db.select([Request.buyer_id.label("buyer_id"), request_count])

...     .group_by(Request.buyer_id)

...     .having(request_count > 0)

...     .cte("request_counts")

... )

>>> buyers_w_counts = Buyer.query.join(cte, cte.c.buyer_id == Buyer.id)

>>> for buyer, count in buyers_w_counts.add_column(cte.c.request_count):

...     # print out buyer and request count for this demo

...     print(buyer, count, sep=": ")

<Buyer foo>: 3

<Buyer bar>: 5

<Buyer baz>: 1

<Buyer spam>: 1

>>> min_request_count = db.select([db.func.min(cte.c.request_count)]).as_scalar()

>>> buyers_w_counts.filter(cte.c.request_count == min_request_count).all()

[<Buyer baz>, <Buyer spam>]

I've also created a db<>fiddle here, containing the same queries, to play with.