sql alchemy: return all unique types of great great grandchildren

I have 6 tables. I am essentially trying to return all unique types of great great grandchildren

How do I return a list of all the unique types of sku_numbers in a FreightDomesticOrder?

Table Definitions:

class FreightOrderDomestic(db.Model):
"""
  A shipment of products from a manufacturer to a fulfillment center
"""
__tablename__ = 'Freight_Order_Domestic'
id = db.Column(db.Integer, primary_key=True, nullable=False)


class Pallet(db.Model):
"""
  An individual Pallet (full of individual cases)
"""
__tablename__ = 'Pallet'
id = db.Column(db.Integer, primary_key=True, nullable=False)

freight_order_fkey = db.ForeignKey("Freight_Order_Domestic.id")
freight_order_id = db.Column(db.Integer, freight_order_fkey, nullable=False)



class OuterCase(db.Model):
"""
 An outer case (full of inner cases)
"""
__tablename__ = 'Outer_Case'
id = db.Column(db.Integer, primary_key=True, nullable=False)

pallet_fkey = db.ForeignKey("Pallet.id")
pallet_id = db.Column(db.Integer, pallet_fkey, nullable=False)



class InnerCase(db.Model):
"""
 An individual case (full of individual items)
"""
__tablename__ = 'Inner_Case'
id = db.Column(db.Integer, primary_key=True, nullable=False)

outer_case_fkey = db.ForeignKey("Outer_Case.id")
outer_case_id = db.Column(db.Integer, outer_case_fkey, nullable=False)



class Each(db.Model):
"""
  An individual item
"""
__tablename__ = 'Each'
id = db.Column(db.Integer, primary_key=True, nullable=False)

inner_case_fkey = db.ForeignKey("Inner_Case.id")
inner_case_id = db.Column(db.Integer, inner_case_fkey, nullable=False)

sku_fkey = db.ForeignKey("Sku.id")
sku_id = db.Column(db.Integer, sku_fkey, nullable=False)



class Sku(db.Model):
"""
  The SKU of an product, the attributes it should have to determine pricing
"""
__tablename__ = 'Sku'

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

sku_number = db.Column(db.String(255), nullable=False)

Here is what I am trying so far but I am stuck, I am also wondering how cheap I can make this:

SKUs = Session.query(Pallet, Outer_case, Inner_case, Each, Sku).filter(Pallet.id == Outer_case.pallet_id).filter(Outer_case.id == Inner_case.outer_case_id).filter(Inner_case.id == Each.inner_case_id).filter(Each.sku_id == sku.id).all()

My other idea was to loop through all Pallets and then Outer_cases and so on but that seems too expensive.

Edited post after table definitions:

Given your table definitions, this should work:

SKUs = session.query(Sku.sku_number)
             .join(Each).join(InnerCase)
             .join(OuterCase).join(Pallet)
             .join(FreightOrderDomestic)
             .filter(FreightOrderDomestic.id == myOrderNumber)
             .group_by(Sku).all()

However, looking at your table definitions I have some other comments that will hopefully help:

• You should setup relationships between the tables, so you can easily work with the different objects. Check out the sqlalchemy documentation on relationships here
• I would suggest reading up on Database Normalization. This will help you understand some of the below points
• You currently have Each setup so there will be duplicate items of the same type if they are assigned to different InnerCases. This is not a good database practice. You should setup this relationship as a many to many relationship, and you can read about that here. This will allow you to have a list of items, and each item can link to many different InnerCases
• FreightDomesticOrder should have a column for order number. You don't want to use a key value as an order number
• If you are going to handle international orders also, you should probably just create a type field for FreightOrderDomestic and rename it to FreightOrder

Original Post:

Can you provide the table definitions? This is very hard to answer accurately without seeing the relationships you have setup. Something like this could work, if you setup your tables like I would have given the description you gave, or it could not work because you didn't provide enough info:

SKUs = session.query(Sku.sku_number)
             .join(Each).join(Inner_case)
             .join(Outer_case).join(Pallet)
             .join(Freight_order)
             .filter(Freight_order.order_number == myOrderNumber)
             .group_by(Sku).all()