Xata•5mo ago

Unable to get file attachment url when using xata.sql

When using xata.sql, I am not able to get the image url in the returned object, I get other attributes like enablePublicUrl: true, id: XXX', mediaType: 'image/webp', name: '759662.webp', signedUrlTimeout: 60, size: 12844, storageKey: 'XXX', uploadKey: 'XXX', uploadUrlTimeout: 86400, version: 1 }

3 Replies

kostas•5mo ago

Hi, currently it is only possible to get urls via the SDK/REST API, but not via the SQL or wire protocol endpoints. Postgres itself is not "aware" of urls, as these are handled by an additional Xata component that combines Postgres records and S3 objects, handles url generation, validation & expiration. We're already working on the next iteration which will be about making Postgres aware of URLs so you don't have to call the SDK/REST API, but this will be available later on. For now you'll need to stick with the SDK calls such as .select(['file.url']).getFirst(); to retrieve and handle urls. Docs reference: https://xata.io/docs/sdk/file-attachments#query-files-metadata

File attachments

Store files within your database with Xata

Raveen•5mo ago

So in that case how can I filter the rows with images length is more than 1 ? I did it like this with the sql: WHERE jsonb_array_length(s.imgs) < 1; How can I do this in SDK ?

kostas•5mo ago

Unfortunately the SDK does not provide an expression for the array length, there is an open feature request for that https://feedback.xata.io/feature-requests/p/array-length-filter-operators. Two steps are needed: perform the query with SQL implementing the array filter, then get the record ids from that response and apply them as a filter in an SDK call (https://xata.io/docs/sdk/filtering#control-operators)

const records = await xata.db.tablename.select(["id", "file.url"]).filter({
  id: { $any: ['array_of_ids'] }
}).getMany();

const records = await xata.db.tablename.select(["id", "file.url"]).filter({
  id: { $any: ['array_of_ids'] }
}).getMany();

Gaming

Programming

Unable to get file attachment url when using xata.sql