Elixir ecto subquery11/18/2023 ![]() ![]() We can do that with the array_to_string function.but that function returns a mutable string. I also have a boolean field to indicate presentations that I've given and if somebody searches for "presentation", I'd like that field to be given more weight.Īs an added twist my tags column is a postgres array which we need to convert to a string to include in our search. If I include a word or term in the title or tag that's probably more important than if it just happens to be included in the body of the text so I want to weight the fields in the search_vector. Now, my search includes 4 different fields: title, introduction, main_body and tags. WHERE search_vector to_tsquery('english','postgres') Which will allow us to just compare against the column knowing the proper index will be used once we have some data in there, like so. Ecto doesn't have anything built in for every piece of PostgreSQL functionality like GIN indexes, so for that we fall back to raw SQL with execute.Įxecute "CREATE INDEX article_search_index ON articles USING GIN(search_vector)" In order to solve the complexity problem and remove any ambiguity, we create a tsvector column with our migration and set an index on that column. If you're including multiple columns and potentially weighting them for priority it's going to get out of hand fast. If you're only including a single column in your search, that's probably not a big deal. Now when I include the above statement in the where clause of a query it will use the index but the downside is that I have to pass that huge chunk into the query every single time for it to get picked up. ![]() ![]() USING GIN (to_tsvector('english', title || ' ' || intro || ' ' || main_body)) You can also create an index with the result of the tsvector like this. That requires running the tsvector function on every row in a sequential scan. WHERE to_tsvector('english', title || ' ' || intro || ' ' || main_body) to_tsquery('english', 'postgres') When we run our search we compare a tsquery to a tsvector, which you can do on the fly like this. The documentation itself provides excellent step by step instructions but here is how we setup ours. In the interest of being thorough let's cover the entire setup process for PostgreSQL full text search. I'm just not as good at macros in Elixir/Ecto to do it (yet).In our last article I skimmed over the details of setting up PostgreSQL search with Ecto. The list of fields is known at compile time. I think technically it is possible to rely on _schema_(:fields) instead of listing all fields explicitly. Questions = Enum.map_join(fields, ", ", fn _ -> "?" run do This is what I got so far: defmodule Magic do I also made an attempt to make it more flexible by using Badge._schema_(:fields) and Badge._schema_(:source), but stumbled upon the inability of fragment to accept variable number of arguments. The source code of the app I used as a playground is there. I tried this approach myself on a simplified example (without a join). ![]() I don't know your schema, so I "made up" 3 columns - I hope you get the idea. The number of question marks in json_agg should be exactly the same as the number of columns in the profiles table and also the order of columns in the table should correspond to the order of fragment arguments. Join: link in FolderMembership, on: link.profile_id = p.id, The solution is not perfect because it requires listing all fields explicitly and also doesn't let you exclude fields from the resulting JSON. ![]()
0 Comments
Leave a Reply.AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |