'Get parents with ALL children that satisfy the filter in sqlachemy orm

Let's say I have a tables Parents and Children in sqlalchemy

If I say db.query(Trip).join(Children,Trip.childrens).filter(Children.color=='red') it returns:

[
{
    id:10,
    childrens:[
        {
            parent_id:10,
            id:15,
            color:'red'
        },
        {
            parent_id:10,
            id:15,
            color:'red'
        }
    ]
},

{
    id:12,
    childrens:[
        {
            parent_id:12,
            id:15,
            color:'red'
        },
        {
            parent_id:12,
            id:15,
            color:'blue'
        },
        {
            parent_id:12,
            id:15,
            color:'blue'
        }
    ]
}
]

However, I need only Parents whose children have only 'red' color

Thank you in advance.



Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source