How to query file reference data type?

[Tim_Jones]

I have a data table with a column “Avatar” with a data type of file reference -

This is an example row of the data returned in the REST console -

If I query that same row, with a WHERE on the Avatar column, I get no results -

If I look at the data in the Data Browser tab, the file reference is different -

If I query that file reference in the rest console, I still get no results -

What am I missing?

[mark-piller]

I’d start with this:

File Reference Data Type | Backendless Database Training Course (pt. 10)

The file reference stored in the database doesn’t contain the hostname - it is calculated dynamically based on the host used in the request.

Regards,
Mark

[Tim_Jones]

Thanks, @mark-piller. That was helpful.

In the Avatar column, I store references to Backendless files and external references. Here is an example -

I want to select all records in the table where the file is hosted on backendless. I’ve come up with -

Avatar <> '' AND Avatar NOT LIKE 'https://cdn.meadowfarms.com%'

Is there a better way?

[mark-piller]

References for files stored outside of Backendless should have the domain name in the URL. It means your query should work. Have you tried? Did it work?

[Tim_Jones]

I did try it, and it does work. What I am less sure about with Backendless, in general, is if I’m doing it the “right” or “smart” way.

I appreciate it when you, or any of the other team, comments on the best way to do something as sometimes it’s not how I would have done it (example - Combining objects from two list of objects).

Tim

[mark-piller]

@Tim_Jones

if I’m doing it the “right” or “smart” way

I think this is true in programming in general

[Tim_Jones]

For sure! I use a lot of StackOverflow examples to learn in other languages.

[stanislaw.grin]

Hi @Tim_Jones,

we don’t have a built-in function to differentiate between files that are hosted in Backendless and files that are hosted from outside, so there will be only one “smart” and “correct” approach here - the way you wrote. However, if you add a third-party file that will have a different domain than the other third-party files, you will need to add this to the where-clause condition in order to exclude it from the results. Not very elegant in terms of code, but will work. Alternatively, you can create an additional column in the table of type Boolean that will identify where the file is located and fetch data based on the value of that column. But this is if you know for sure that a particular entry has a file that is hosted specifically in the Backendless or somewhere outside and it will not be changed dynamically.

Regards,
Stanislaw

[mark-piller]

I did it a test with “generated column” which can automatically produce the result indicating if a file is stored internally or externally:

The generated column’s function looks like this:

Hope this helps.

Mark

[Tim_Jones]

Thanks @stanislaw.grin! I appreciate the additional details.

Thanks @mark-piller for a very cool solution!

I really appreciate the effort you put into helping me (and everyone else) with Backendless.

Best,
Tim