Query filePath and EcInstanceId

[zhang-zimin]

I'm working with iTwin.js and need to find the corresponding ECInstanceId and filePath for a given sourceElementId. After reviewing the documentation, I haven't found a direct API for this specific use case. Could you advise on the recommended approach?

[grigasp]

Hi @zhang-zimin.

You will need to use an ECSQL query to get this information. To run the query on the frontend, you can use the IModelConnection.createQueryReader function.

The query could look something like this:

SELECT
    a.Element.Id AS ITwinElementId,
    COALESCE(
        (
            SELECT rl.Url
            FROM bis.RepositoryLink rl
            JOIN bis.ExternalSource es ON es.Repository.Id = rl.ECInstanceId
            WHERE es.ECInstanceId = a.Source.Id
            LIMIT 1
        ), (
            SELECT rl.Url
            FROM bis.RepositoryLink rl
            JOIN bis.ElementHasLinks ehl ON ehl.TargetECInstanceId = rl.ECInstanceId
            JOIN bis.Model m ON m.ModeledElement.Id = ehl.SourceECInstanceId
            JOIN bis.Element e ON e.Model.Id = m.ECInstanceId
            WHERE e.ECInstanceId = a.Element.Id
            LIMIT 1
        )
    ) AS FilePath
FROM bis.ExternalSourceAspect a
WHERE a.Identifier = '<your element identifier here>'

CC @diegoalexdiaz in case the query can be improved.

[diegoalexdiaz]

I would expect the first sub-query passed to COALESCE to be sufficient these days as all connectors I'm aware of capture it that way.

The second sub-query covers the original way (several years ago) to capture that information, but to my knowledge, maybe only a couple of Connectors still do it for backward compatibility, but they also do it in the way covered by the first sub-query.

So, in summary, if just with the first sub-query covers your use-case, there shouldn't be a need to add the second one (and therefore, no need for the COALESCE function either).