Filter SQL rows that may be null

Question

I'm trying to write a query in Kotlin Exposed that joins multiple tables together. Some of the tables can have null values, in which case the select statement should just ignore those.

On an example: I have a UserTable, a GeoLocationTable and a PhotoTable. A user will always have a referenced GeoLocation table, but it may or may not have photos. A UserTable doesn't know anything about the PhotoTable, but the PhotoTable has a userId as a FK.

I want to achieve that, when I query for the user - I always receive a user in the result set. The photos should only be in the result set if there are photos that have userId as a foreign key, and otherwise the result set should only contain the user.

My problem is that if photos for the user are not in the database, then my query doesn't even return the user! What am I doing wrong?

Here is the query.

    private fun fetchUserWithPhotos(userId: String) = tx {
        val query = UserProfileTable
            .join(
                joinType = JoinType.LEFT,
                otherTable = GeoLocationTable,
                otherColumn = GeoLocationTable.id,
                onColumn = UserProfileTable.geoLocationId
            )
            .join(
                joinType = JoinType.LEFT,
                otherTable = PhotoTable,
                otherColumn = PhotoTable.userId,
                onColumn = UserProfileTable.id
            )

        val x = query
            .select {
                (UserProfileTable.id eq userId) and
                    (UserProfileTable.deletedAt.isNull()) and
                    (UserProfileTable.enabled eq true) and
                    (PhotoTable.userPhotoType eq UserPhotoType.PROFILE.toString()) and
                    (PhotoTable.position eq 1)
            }
        // x is empty set here, even though the user EXISTS!
    }

How can I always get the user, and photos only if they are present?

Answer 1

I think I have this straight, the query I can parse from your code works out to this:

select * from user_profile
left join geo_location on user_profile.geo_location_id = geo_location.id
left join photo on user_profile.id = photo.user_id
where user_profile.id = ? 
and user_profile.deleted_at is null 
and user_profile.enabled  is true
and photo.user_photo_type = 'PROFILE'
and photo.position = 1;

Your issue as described is: ' if photos for the user are not in the database, then my query doesn't even return the user! What am I doing wrong?'

Issue: You are using predicates based on data in the photo table, you've stated there isn't always a photo entry for a user. If there are no photos then the predicates are false and that row won't be selected, even if you know the user exists:

and photo.user_photo_type = 'PROFILE'
and photo.position = 1;

Proposed Solution: I think you can try to join photos that you want and maintain the predicates on the user table only. Update your query to:

select * from user_profile
left join geo_location on user_profile.geo_location_id = geo_location.id
left join photo on user_profile.id = photo.user_id and photo.position = 1 and photo.user_photo_type = 'PROFILE'
where user_profile.id = ? 
and user_profile.deleted_at is null 
and user_profile.enabled  is true;