Closed UTXOnly closed 8 months ago
This query seems to yield the results we want from the tags
column:
SELECT *
FROM events
WHERE EXISTS (
SELECT 1
FROM jsonb_array_elements(tags) as elem
WHERE elem::text LIKE '%charity%'
)
ORDER BY created_at
LIMIT 100;
Something as simple as:
SELECT * from events WHERE content LIKE '%charity%';
Works for the content
search
Commit 1efc386bab465391584903658c86605e1283c9d2 tested and supports search for both tags
and content
columns. Confirmed with self made command line client. Would like to test with some real clients before releasing.
Filters sent from client to relay
2024-02-18 18:12:53 INFO: Query sent: ["REQ", "5326483051590112", {"search": "search", "kinds": [1, 7, 9735], "limit": 300, "since": 1708297853, "authors": ["b97b26c3ec44390727b5800598a9de42b222ae7b5402abcf13d2ae8f386e4e0c", "d576043ce19fa2cb684de60ffb8fe529e420a1411b96b6788f11cb0442252eea"]}]
2024-02-18 18:12:53 INFO: Response from websocket server: ["EVENT", "5326483051590112", {"id": "3bf4db1f936aa6da2bb011f9589f7f52d233a92af671e3029549dc17e0453338", "pubkey": "e10a37a0b0a59bf24aaae98cf668f10f3408f4e13e6862c9e389e8f04d19de47", "kind": 1, "created_at": 1707221667, "tags": [["e", "5282f08000e8fe26cd9e469f8111ed84330f22221e0b9d5d3f3bc20ad0d9b81d", "", "root"], ["e", "8fa153d38f998670501f5e25ba329e1c7e3c0cdbe7447aa584b9a7f17d893b74"], ["e", "38fb3154093c46b2a45631f3f945b293edb7eba8aa82adbe430f1848f4325c93", "", "reply"], ["p", "22e608979b7eb9f58074da8fc14ec2c86dbb012d91474d5f040601bbbfc3014b"], ["p", "e10a37a0b0a59bf24aaae98cf668f10f3408f4e13e6862c9e389e8f04d19de47"], ["r", "alternativeto.net"]], "content": "What kind of programs do you use? While it is true that not all programs you have used in Windows are available/functional (Wine (with Bottles) or native Linux apps), there are plenty of alternatives that do work.\n\nalternativeto.net is a great resource you can search \ud83d\udcaa\ud83d\ude0e", "sig": "be48f21f22c895283a0d787e966b27cf6cba2a032e0bb4b501bf33e51386a53a5c3d191cf206a32e83d8cb2684a9d274c7c41ed3a1d7bcb8066a7c06edd9bad7"}]
Example of how SQL query was formatted correctly
2024-02-18 23:13:14,213 - DEBUG - SQL query constructed: SELECT * FROM events WHERE kind = ANY(ARRAY [1, 7, 9735]) AND created_at > 1708297857 AND pubkey = ANY(ARRAY ['b97b26c3ec44390727b5800598a9de42b222ae7b5402abcf13d2ae8f386e4e0c', 'd576043ce19fa2cb684de60ffb8fe529e420a1411b96b6788f11cb0442252eea']) AND EXISTS ( SELECT 1 FROM jsonb_array_elements(tags) as elem WHERE elem::text LIKE '%search%') OR content LIKE '%search%' ORDER BY created_at LIMIT 300 ;
Background
NIP-50 enables clients to add a search filter included in their
REQ
queries. The spec only requires searchingcontent
field and the rest is optional and largely up to the relay operator to decide how to implement it.Motivation
End goal is trying to support better search functionality for kind
30402
(Classified Listings, NIP-99) Example: If an event have a tag["title", "This is my charity listing"]
, when searching"charity"
, it should match that event.User story
As any user, I would like to enter a search string into a NOSTR client search bar and have relays return events where my search string is present in either the
content
ortags
section of the eventPossible solutions
Nostpy already supports tag search but only if the entire filter is present as a list item within the
tags
array.Link to tag query builder method
For example, if passed the filter
["p", "e731ca427c18059d66636ddfaeeeb15012bc2db3cdd27b9e4cade5057a6e82ed"]
, the method above would produce a query like below:The SQL function int he query only returns the event is the column in contains an array in the
tags
column that matches["p", "e731ca427c18059d66636ddfaeeeb15012bc2db3cdd27b9e4cade5057a6e82ed"]
exactly.I think it would be easiest to extract the search string in the method below (like we do for limit) to remove it from the loop that transforms other
filters
into SQL clauses and create a new separate method likegenerate_tag_clause
but using theLIKE '%a%'
to search thevalue
of thetag
key:value
pair to return events where this is only a partial match.TO DO
Subscription
class that:{"search": "..."}
filter dictionary on it's ownparse_sanitized_keys
method to search for{"search": "..."}
within another filter dictionary (Most clients don't seem to support this, will revisit under a future FR)["REQ", "", { "search": "orange" }, { "kinds": [1, 2], "search": "purple" }]
search
filters into SQL clauses like shown above