How to search for a keyword in a User's casts

Your own Warpcast advance search on dune

One of the things I don't like about Warpcast is its limited search functionality. If you search for a keyword on Warpcast, you only get three tabs for Casts, Channels, and Users. as you can see in this picture:

So what can we do about that right now? Farcaster is a sufficiently decentralized platform so you can make your own client for it that has advanced search features. I can't do that so I write a query on the dune.neynar.dataset_farcaster_casts database on dune.com for a certain keyword, the keyword here is Gloom because that's one of my favorite based tokens:

select * from dune.neynar.dataset_farcaster_casts where text like '%gloom%'

This is a simple, single SQL query. % is a sql wildcard that represents zero, one, or multiple characters. this query returns more than 5000 casts:

we can fine-tune this further, for example, let's see how many casts the one and only @bixbite, founder of gloom has about gloom, we don't want to complicate the query so we use her FID, and we order them by the crated at column to get chronological order:

select * from dune.neynar.dataset_farcaster_casts where text like '%gloom%' and fid = 20147 order by created_at

we get around 250 results and looking at the rows with the oldest casts we can see that this database currently only contains casts from fab 14th 2024 and onward:

two other columns in the cast database can be interesting, root_parent_hash and root_parent_url, looking at the schema doc (you can find it here) these two columns are defined as:

root_parent_hash

text

If this cast was a reply, then the URL that the original cast in the reply chain was replying to.

parent_url

text

If this cast was a reply to a URL (e.g. an NFT, a web URL, etc.), the URL. null otherwise.

so let's query these two columns alone and see what's in them:

select parent_url, root_parent_url  from dune.neynar.dataset_farcaster_casts where text like '%gloom%' and fid = 20147 order by created_at desc

Those do not like the URL of the original cast in case these casts were replies. Those are in fact as far as I can tell, Channel URLs and for some reason, some channels have a normal URL like https://warpcast.com/~/channel/gloomtoken, and some channels have a not-so-normal looking URL like chain://eip155:1/erc721:0x9c8ff314c9bc7f6e59a9d9225fb22946427edc03 which is the nouns channel on farcaster. You can find these URLs in this JSON file and using that you can query the casts in specific channels. For example, I query my own casts on the paidgroup channel:

select *  from dune.neynar.dataset_farcaster_casts where root_parent_url = 'https://warpcast.com/~/channel/paidgroup' and fid = 291065 order by created_at desc

And I can see all my casts on the paid group channel:

you cand find this query and paly with it here. If you want to use the keyword query just create a new query on dune.com and copy it from here and change the keyword and fid. you can find me as @miladgh on Farcaster if you have any questions and comments about this post.

Loading...
highlight
Collect this post to permanently own it.
Subscribe to Tinkering Onchain and never miss a post.
  • Loading comments...