Polymorphic Fields

Comments

2 comments

  • Avatar
    Evan Adnams

    Well, a short term solution was to convert nulls to zeros, and make a zero User and org ID. So the left Join always has a connecting row.

    CASE
    WHEN [owner_type] = 'Organization' THEN [owner_id]
    ELSE 0
    end

    and 

    SELECT * FROM [Orgs]
    UNION
    SELECT * FROM [Rapid org 0.csv]

    So I add the row of Zeros ;)

    0
    Comment actions Permalink
  • Avatar
    Richard Stubbs (Edited )

    Hi,

    If I've understood the problem correctly, I believe there is a simpler way to achieve what you want.

    Assuming the table that contains owner_type and owner_id is an imported table (rather than a custom table), then adding a custom field using the Elasticube function "lookup" should do the trick

    CASE
    WHEN owner_type LIKE 'Organization' THEN lookup(org, name, owner_id, id)
    WHEN owner_type LIKE 'User' THEN lookup(user, name, owner_id, id)
    END

    (assuming "name" is the field containing the user/org names in those tables)

    This will give the name, taken from either the org or the user table for each row (whichever is appropriate depending on the owner_type). You then don't need to (in fact you shouldn't) connect owner_id to anything.

    However, if the table containing owner_type and owner_id is a custom table generated from a query that you've written, then I don't think lookup() will work. It can still be done by joining user and org to the other tables in the query though.

    0
    Comment actions Permalink

Please sign in to leave a comment.