Utilizing a query in a relation

[nbastiantgc]

Hello everyone,

I recently started using Drizzle in one of my projects. I am now in a situation, where I need to define a relation based on an sql query.

Here is an example data structure:

table 'user'
id primary key,
first_name string,
last_name string

table 'tag'
id primary key,
entity string,
entity_id int,
value string

My use-case is, that I want to be able to add tags to different entities (like 'user'). Instead of defining one tag table for each relation, I want to use one unified tag table, which contains the associated entity (column 'entity') in the form of the table name and the primary key value (column 'entity_id').

If I want to get all tags for an entity or a list of entities, I want to be able to just do something like:

db.query.users.findMany({
    with: {
      tags: true
    }
});

I image it working similar to Hibernate Formular Annotation (https://medium.com/jpa-java-persistence-api-guide/hibernate-sub-query-with-formula-annotation-ace1085717c8).

Does anyone know if something like this is possible with Drizzle or if it's planned for a future release?

Thanks in advance!

[nbastiantgc]

I came up with the following solution:

Since my 'tag' table may consist tags for various entities in different tables, i created multiple relations for 'tag':

export const tagRelations = relations(tagTable, ({one}) => ({
    user: one(usersTable, {
        fields: [tagTable.entityId],
        references: [usersTable.id]
    }),
    resource: one(resourceTable, {
        fields: [tagTable.entityId],
        references: [resourceTable.id]
    }),
// and so on...
}));

Unfortunately, I cannot use static strings in relations, which would result in something like this:

export const tagRelations = relations(tagTable, ({one}) => ({
    user: one(usersTable, {
        fields: [tagTable.entityId, tagTable.entity],
        references: [usersTable.id, 'user']
    }),
    resource: one(resourceTable, {
        fields: [tagTable.entityId, tagTable.entity],
        references: [resourceTable.id, 'resource']
    }),
// and so on...
}));

Since the above example with static strings is not possible, I then need to use a where condition when including tags in my query.
Here is an example:

dbUser.query.resourceTable.findFirst({
        where: eq(resourceTable.id, idFromRequest),
        with: {
            tags: {
                where: eq(tagTable.entity, 'resource')
            }
        }
    });

Maybe this might help someone else in the future.