Why does pg_search prefix not work like I expect?

707 views Asked by At

This is my pg_search method:

  pg_search_scope :node_search, against: [:name, :user_id, :circa, :cached_tagged_user_names, :cached_user_tag_list],
    using: { tsearch: { any_word: true, dictionary: :english, prefix: true} },
    :associated_against => {
      comments: [:message],
      user: [:first_name, :last_name, :email],
      memberships: [:relation],
      user_tags: [:name]
    }

Not the prefix: true for tsearch like the docs recommend.

However, at least in 1 particular case, it doesn't work like I expect.

I have a Node object that looks like this:

=> [#<Node id: 85, name: "House Fire 2", family_tree_id: 57, user_id: 57, media_id: 228, media_type: "Video", created_at: "2015-05-15 00:20:26", updated_at: "2015-06-08 22:25:49", circa: nil, is_comment: nil, cached_votes_total: 0, cached_votes_score: 0, cached_votes_up: 0, cached_votes_down: 0, cached_weighted_score: 0, cached_weighted_total: 0, cached_weighted_average: 0.0, cached_user_tag_list: "[email protected], [email protected]", cached_num_user_tags: 2, cached_tagged_user_names: ["Daniel Marty", "Marcamus Prime"]>]

Note the attribute: cached_user_tag_list that includes the email address: [email protected].

Yet, when I do the following searches these are the results I get:

[185] pry(main)> Node.node_search("dann")
  Node Load (11.3ms)  SELECT "nodes".*, ((ts_rank((to_tsvector('english', coalesce("nodes"."name"::text, '')) || to_tsvector('english', coalesce("nodes"."user_id"::text, '')) || to_tsvector('english', coalesce("nodes"."circa"::text, '')) || to_tsvector('english', coalesce("nodes"."cached_tagged_user_names"::text, '')) || to_tsvector('english', coalesce("nodes"."cached_user_tag_list"::text, '')) || to_tsvector('english', coalesce(pg_search_121ea89914a721445aee70.pg_search_344e3c62d13849726da22e::text, '')) || to_tsvector('english', coalesce(pg_search_4aab10cdca183dac25f479.pg_search_acfcdbc0b3d3a65f40eab7::text, '')) || to_tsvector('english', coalesce(pg_search_4aab10cdca183dac25f479.pg_search_eeb842708b24d4f7fcf549::text, '')) || to_tsvector('english', coalesce(pg_search_4aab10cdca183dac25f479.pg_search_cb8eb1b84bf18ee1412ffd::text, '')) || to_tsvector('english', coalesce(pg_search_b1bc4c0f44e7f4799d8caf.pg_search_9474fb5b090e75ade5136f::text, '')) || to_tsvector('english', coalesce(pg_search_93dd21271636bd02b16bc3.pg_search_484f77386a5aecc6e01094::text, ''))), (to_tsquery('english', ''' ' || 'dann' || ' ''' || ':*')), 0))) AS pg_search_rank FROM "nodes" LEFT OUTER JOIN (SELECT "nodes"."id" AS id, string_agg("comments"."message"::text, ' ') AS pg_search_344e3c62d13849726da22e FROM "nodes" INNER JOIN "comments" ON "comments"."node_id" = "nodes"."id" GROUP BY "nodes"."id") pg_search_121ea89914a721445aee70 ON pg_search_121ea89914a721445aee70.id = "nodes"."id" LEFT OUTER JOIN (SELECT "nodes"."id" AS id, string_agg("users"."first_name"::text, ' ') AS pg_search_acfcdbc0b3d3a65f40eab7, string_agg("users"."last_name"::text, ' ') AS pg_search_eeb842708b24d4f7fcf549, string_agg("users"."email"::text, ' ') AS pg_search_cb8eb1b84bf18ee1412ffd FROM "nodes" INNER JOIN "users" ON "users"."id" = "nodes"."user_id" GROUP BY "nodes"."id") pg_search_4aab10cdca183dac25f479 ON pg_search_4aab10cdca183dac25f479.id = "nodes"."id" LEFT OUTER JOIN (SELECT "nodes"."id" AS id, string_agg("memberships"."relation"::text, ' ') AS pg_search_9474fb5b090e75ade5136f FROM "nodes" INNER JOIN "family_trees" ON "family_trees"."id" = "nodes"."family_tree_id" INNER JOIN "memberships" ON "memberships"."family_tree_id" = "family_trees"."id" GROUP BY "nodes"."id") pg_search_b1bc4c0f44e7f4799d8caf ON pg_search_b1bc4c0f44e7f4799d8caf.id = "nodes"."id" LEFT OUTER JOIN (SELECT "nodes"."id" AS id, string_agg("tags"."name"::text, ' ') AS pg_search_484f77386a5aecc6e01094 FROM "nodes" INNER JOIN "taggings" ON "taggings"."taggable_id" = "nodes"."id" AND "taggings"."taggable_type" = 'Node' AND "taggings"."context" = 'user_tags' INNER JOIN "tags" ON "tags"."id" = "taggings"."tag_id" GROUP BY "nodes"."id") pg_search_93dd21271636bd02b16bc3 ON pg_search_93dd21271636bd02b16bc3.id = "nodes"."id" WHERE (((to_tsvector('english', coalesce("nodes"."name"::text, '')) || to_tsvector('english', coalesce("nodes"."user_id"::text, '')) || to_tsvector('english', coalesce("nodes"."circa"::text, '')) || to_tsvector('english', coalesce("nodes"."cached_tagged_user_names"::text, '')) || to_tsvector('english', coalesce("nodes"."cached_user_tag_list"::text, '')) || to_tsvector('english', coalesce(pg_search_121ea89914a721445aee70.pg_search_344e3c62d13849726da22e::text, '')) || to_tsvector('english', coalesce(pg_search_4aab10cdca183dac25f479.pg_search_acfcdbc0b3d3a65f40eab7::text, '')) || to_tsvector('english', coalesce(pg_search_4aab10cdca183dac25f479.pg_search_eeb842708b24d4f7fcf549::text, '')) || to_tsvector('english', coalesce(pg_search_4aab10cdca183dac25f479.pg_search_cb8eb1b84bf18ee1412ffd::text, '')) || to_tsvector('english', coalesce(pg_search_b1bc4c0f44e7f4799d8caf.pg_search_9474fb5b090e75ade5136f::text, '')) || to_tsvector('english', coalesce(pg_search_93dd21271636bd02b16bc3.pg_search_484f77386a5aecc6e01094::text, ''))) @@ (to_tsquery('english', ''' ' || 'dann' || ' ''' || ':*'))))  ORDER BY pg_search_rank DESC, "nodes"."id" ASC
=> [#<Node id: 85, name: "House Fire 2", family_tree_id: 57, user_id: 57, media_id: 228, media_type: "Video", created_at: "2015-05-15 00:20:26", updated_at: "2015-06-08 22:25:49", circa: nil, is_comment: nil, cached_votes_total: 0, cached_votes_score: 0, cached_votes_up: 0, cached_votes_down: 0, cached_weighted_score: 0, cached_weighted_total: 0, cached_weighted_average: 0.0, cached_user_tag_list: "[email protected], [email protected]", cached_num_user_tags: 2, cached_tagged_user_names: ["Daniel Marty", "Marcamus Prime"]>]
[186] pry(main)> Node.node_search("dan")
  Node Load (4.2ms)  SELECT "nodes".*, ((ts_rank((to_tsvector('english', coalesce("nodes"."name"::text, '')) || to_tsvector('english', coalesce("nodes"."user_id"::text, '')) || to_tsvector('english', coalesce("nodes"."circa"::text, '')) || to_tsvector('english', coalesce("nodes"."cached_tagged_user_names"::text, '')) || to_tsvector('english', coalesce("nodes"."cached_user_tag_list"::text, '')) || to_tsvector('english', coalesce(pg_search_121ea89914a721445aee70.pg_search_344e3c62d13849726da22e::text, '')) || to_tsvector('english', coalesce(pg_search_4aab10cdca183dac25f479.pg_search_acfcdbc0b3d3a65f40eab7::text, '')) || to_tsvector('english', coalesce(pg_search_4aab10cdca183dac25f479.pg_search_eeb842708b24d4f7fcf549::text, '')) || to_tsvector('english', coalesce(pg_search_4aab10cdca183dac25f479.pg_search_cb8eb1b84bf18ee1412ffd::text, '')) || to_tsvector('english', coalesce(pg_search_b1bc4c0f44e7f4799d8caf.pg_search_9474fb5b090e75ade5136f::text, '')) || to_tsvector('english', coalesce(pg_search_93dd21271636bd02b16bc3.pg_search_484f77386a5aecc6e01094::text, ''))), (to_tsquery('english', ''' ' || 'dan' || ' ''' || ':*')), 0))) AS pg_search_rank FROM "nodes" LEFT OUTER JOIN (SELECT "nodes"."id" AS id, string_agg("comments"."message"::text, ' ') AS pg_search_344e3c62d13849726da22e FROM "nodes" INNER JOIN "comments" ON "comments"."node_id" = "nodes"."id" GROUP BY "nodes"."id") pg_search_121ea89914a721445aee70 ON pg_search_121ea89914a721445aee70.id = "nodes"."id" LEFT OUTER JOIN (SELECT "nodes"."id" AS id, string_agg("users"."first_name"::text, ' ') AS pg_search_acfcdbc0b3d3a65f40eab7, string_agg("users"."last_name"::text, ' ') AS pg_search_eeb842708b24d4f7fcf549, string_agg("users"."email"::text, ' ') AS pg_search_cb8eb1b84bf18ee1412ffd FROM "nodes" INNER JOIN "users" ON "users"."id" = "nodes"."user_id" GROUP BY "nodes"."id") pg_search_4aab10cdca183dac25f479 ON pg_search_4aab10cdca183dac25f479.id = "nodes"."id" LEFT OUTER JOIN (SELECT "nodes"."id" AS id, string_agg("memberships"."relation"::text, ' ') AS pg_search_9474fb5b090e75ade5136f FROM "nodes" INNER JOIN "family_trees" ON "family_trees"."id" = "nodes"."family_tree_id" INNER JOIN "memberships" ON "memberships"."family_tree_id" = "family_trees"."id" GROUP BY "nodes"."id") pg_search_b1bc4c0f44e7f4799d8caf ON pg_search_b1bc4c0f44e7f4799d8caf.id = "nodes"."id" LEFT OUTER JOIN (SELECT "nodes"."id" AS id, string_agg("tags"."name"::text, ' ') AS pg_search_484f77386a5aecc6e01094 FROM "nodes" INNER JOIN "taggings" ON "taggings"."taggable_id" = "nodes"."id" AND "taggings"."taggable_type" = 'Node' AND "taggings"."context" = 'user_tags' INNER JOIN "tags" ON "tags"."id" = "taggings"."tag_id" GROUP BY "nodes"."id") pg_search_93dd21271636bd02b16bc3 ON pg_search_93dd21271636bd02b16bc3.id = "nodes"."id" WHERE (((to_tsvector('english', coalesce("nodes"."name"::text, '')) || to_tsvector('english', coalesce("nodes"."user_id"::text, '')) || to_tsvector('english', coalesce("nodes"."circa"::text, '')) || to_tsvector('english', coalesce("nodes"."cached_tagged_user_names"::text, '')) || to_tsvector('english', coalesce("nodes"."cached_user_tag_list"::text, '')) || to_tsvector('english', coalesce(pg_search_121ea89914a721445aee70.pg_search_344e3c62d13849726da22e::text, '')) || to_tsvector('english', coalesce(pg_search_4aab10cdca183dac25f479.pg_search_acfcdbc0b3d3a65f40eab7::text, '')) || to_tsvector('english', coalesce(pg_search_4aab10cdca183dac25f479.pg_search_eeb842708b24d4f7fcf549::text, '')) || to_tsvector('english', coalesce(pg_search_4aab10cdca183dac25f479.pg_search_cb8eb1b84bf18ee1412ffd::text, '')) || to_tsvector('english', coalesce(pg_search_b1bc4c0f44e7f4799d8caf.pg_search_9474fb5b090e75ade5136f::text, '')) || to_tsvector('english', coalesce(pg_search_93dd21271636bd02b16bc3.pg_search_484f77386a5aecc6e01094::text, ''))) @@ (to_tsquery('english', ''' ' || 'dan' || ' ''' || ':*'))))  ORDER BY pg_search_rank DESC, "nodes"."id" ASC
=> [#<Node id: 85, name: "House Fire 2", family_tree_id: 57, user_id: 57, media_id: 228, media_type: "Video", created_at: "2015-05-15 00:20:26", updated_at: "2015-06-08 22:25:49", circa: nil, is_comment: nil, cached_votes_total: 0, cached_votes_score: 0, cached_votes_up: 0, cached_votes_down: 0, cached_weighted_score: 0, cached_weighted_total: 0, cached_weighted_average: 0.0, cached_user_tag_list: "[email protected], [email protected]", cached_num_user_tags: 2, cached_tagged_user_names: ["Daniel Marty", "Marcamus Prime"]>]
[187] pry(main)> Node.node_search("danny")
  Node Load (2.8ms)  SELECT "nodes".*, ((ts_rank((to_tsvector('english', coalesce("nodes"."name"::text, '')) || to_tsvector('english', coalesce("nodes"."user_id"::text, '')) || to_tsvector('english', coalesce("nodes"."circa"::text, '')) || to_tsvector('english', coalesce("nodes"."cached_tagged_user_names"::text, '')) || to_tsvector('english', coalesce("nodes"."cached_user_tag_list"::text, '')) || to_tsvector('english', coalesce(pg_search_121ea89914a721445aee70.pg_search_344e3c62d13849726da22e::text, '')) || to_tsvector('english', coalesce(pg_search_4aab10cdca183dac25f479.pg_search_acfcdbc0b3d3a65f40eab7::text, '')) || to_tsvector('english', coalesce(pg_search_4aab10cdca183dac25f479.pg_search_eeb842708b24d4f7fcf549::text, '')) || to_tsvector('english', coalesce(pg_search_4aab10cdca183dac25f479.pg_search_cb8eb1b84bf18ee1412ffd::text, '')) || to_tsvector('english', coalesce(pg_search_b1bc4c0f44e7f4799d8caf.pg_search_9474fb5b090e75ade5136f::text, '')) || to_tsvector('english', coalesce(pg_search_93dd21271636bd02b16bc3.pg_search_484f77386a5aecc6e01094::text, ''))), (to_tsquery('english', ''' ' || 'danny' || ' ''' || ':*')), 0))) AS pg_search_rank FROM "nodes" LEFT OUTER JOIN (SELECT "nodes"."id" AS id, string_agg("comments"."message"::text, ' ') AS pg_search_344e3c62d13849726da22e FROM "nodes" INNER JOIN "comments" ON "comments"."node_id" = "nodes"."id" GROUP BY "nodes"."id") pg_search_121ea89914a721445aee70 ON pg_search_121ea89914a721445aee70.id = "nodes"."id" LEFT OUTER JOIN (SELECT "nodes"."id" AS id, string_agg("users"."first_name"::text, ' ') AS pg_search_acfcdbc0b3d3a65f40eab7, string_agg("users"."last_name"::text, ' ') AS pg_search_eeb842708b24d4f7fcf549, string_agg("users"."email"::text, ' ') AS pg_search_cb8eb1b84bf18ee1412ffd FROM "nodes" INNER JOIN "users" ON "users"."id" = "nodes"."user_id" GROUP BY "nodes"."id") pg_search_4aab10cdca183dac25f479 ON pg_search_4aab10cdca183dac25f479.id = "nodes"."id" LEFT OUTER JOIN (SELECT "nodes"."id" AS id, string_agg("memberships"."relation"::text, ' ') AS pg_search_9474fb5b090e75ade5136f FROM "nodes" INNER JOIN "family_trees" ON "family_trees"."id" = "nodes"."family_tree_id" INNER JOIN "memberships" ON "memberships"."family_tree_id" = "family_trees"."id" GROUP BY "nodes"."id") pg_search_b1bc4c0f44e7f4799d8caf ON pg_search_b1bc4c0f44e7f4799d8caf.id = "nodes"."id" LEFT OUTER JOIN (SELECT "nodes"."id" AS id, string_agg("tags"."name"::text, ' ') AS pg_search_484f77386a5aecc6e01094 FROM "nodes" INNER JOIN "taggings" ON "taggings"."taggable_id" = "nodes"."id" AND "taggings"."taggable_type" = 'Node' AND "taggings"."context" = 'user_tags' INNER JOIN "tags" ON "tags"."id" = "taggings"."tag_id" GROUP BY "nodes"."id") pg_search_93dd21271636bd02b16bc3 ON pg_search_93dd21271636bd02b16bc3.id = "nodes"."id" WHERE (((to_tsvector('english', coalesce("nodes"."name"::text, '')) || to_tsvector('english', coalesce("nodes"."user_id"::text, '')) || to_tsvector('english', coalesce("nodes"."circa"::text, '')) || to_tsvector('english', coalesce("nodes"."cached_tagged_user_names"::text, '')) || to_tsvector('english', coalesce("nodes"."cached_user_tag_list"::text, '')) || to_tsvector('english', coalesce(pg_search_121ea89914a721445aee70.pg_search_344e3c62d13849726da22e::text, '')) || to_tsvector('english', coalesce(pg_search_4aab10cdca183dac25f479.pg_search_acfcdbc0b3d3a65f40eab7::text, '')) || to_tsvector('english', coalesce(pg_search_4aab10cdca183dac25f479.pg_search_eeb842708b24d4f7fcf549::text, '')) || to_tsvector('english', coalesce(pg_search_4aab10cdca183dac25f479.pg_search_cb8eb1b84bf18ee1412ffd::text, '')) || to_tsvector('english', coalesce(pg_search_b1bc4c0f44e7f4799d8caf.pg_search_9474fb5b090e75ade5136f::text, '')) || to_tsvector('english', coalesce(pg_search_93dd21271636bd02b16bc3.pg_search_484f77386a5aecc6e01094::text, ''))) @@ (to_tsquery('english', ''' ' || 'danny' || ' ''' || ':*'))))  ORDER BY pg_search_rank DESC, "nodes"."id" ASC
=> []

Note that it returns an empty result on the query danny, yet it returns the right records on the query dan and dann.

Given that the string is [email protected], isn't danny a prefix of that string?

Note that for the email address [email protected], the search for abc returns the correct results as seen below:

[188] pry(main)> Node.node_search("abc")
  Node Load (6.9ms)  SELECT "nodes".*, ((ts_rank((to_tsvector('english', coalesce("nodes"."name"::text, '')) || to_tsvector('english', coalesce("nodes"."user_id"::text, '')) || to_tsvector('english', coalesce("nodes"."circa"::text, '')) || to_tsvector('english', coalesce("nodes"."cached_tagged_user_names"::text, '')) || to_tsvector('english', coalesce("nodes"."cached_user_tag_list"::text, '')) || to_tsvector('english', coalesce(pg_search_121ea89914a721445aee70.pg_search_344e3c62d13849726da22e::text, '')) || to_tsvector('english', coalesce(pg_search_4aab10cdca183dac25f479.pg_search_acfcdbc0b3d3a65f40eab7::text, '')) || to_tsvector('english', coalesce(pg_search_4aab10cdca183dac25f479.pg_search_eeb842708b24d4f7fcf549::text, '')) || to_tsvector('english', coalesce(pg_search_4aab10cdca183dac25f479.pg_search_cb8eb1b84bf18ee1412ffd::text, '')) || to_tsvector('english', coalesce(pg_search_b1bc4c0f44e7f4799d8caf.pg_search_9474fb5b090e75ade5136f::text, '')) || to_tsvector('english', coalesce(pg_search_93dd21271636bd02b16bc3.pg_search_484f77386a5aecc6e01094::text, ''))), (to_tsquery('english', ''' ' || 'abc' || ' ''' || ':*')), 0))) AS pg_search_rank FROM "nodes" LEFT OUTER JOIN (SELECT "nodes"."id" AS id, string_agg("comments"."message"::text, ' ') AS pg_search_344e3c62d13849726da22e FROM "nodes" INNER JOIN "comments" ON "comments"."node_id" = "nodes"."id" GROUP BY "nodes"."id") pg_search_121ea89914a721445aee70 ON pg_search_121ea89914a721445aee70.id = "nodes"."id" LEFT OUTER JOIN (SELECT "nodes"."id" AS id, string_agg("users"."first_name"::text, ' ') AS pg_search_acfcdbc0b3d3a65f40eab7, string_agg("users"."last_name"::text, ' ') AS pg_search_eeb842708b24d4f7fcf549, string_agg("users"."email"::text, ' ') AS pg_search_cb8eb1b84bf18ee1412ffd FROM "nodes" INNER JOIN "users" ON "users"."id" = "nodes"."user_id" GROUP BY "nodes"."id") pg_search_4aab10cdca183dac25f479 ON pg_search_4aab10cdca183dac25f479.id = "nodes"."id" LEFT OUTER JOIN (SELECT "nodes"."id" AS id, string_agg("memberships"."relation"::text, ' ') AS pg_search_9474fb5b090e75ade5136f FROM "nodes" INNER JOIN "family_trees" ON "family_trees"."id" = "nodes"."family_tree_id" INNER JOIN "memberships" ON "memberships"."family_tree_id" = "family_trees"."id" GROUP BY "nodes"."id") pg_search_b1bc4c0f44e7f4799d8caf ON pg_search_b1bc4c0f44e7f4799d8caf.id = "nodes"."id" LEFT OUTER JOIN (SELECT "nodes"."id" AS id, string_agg("tags"."name"::text, ' ') AS pg_search_484f77386a5aecc6e01094 FROM "nodes" INNER JOIN "taggings" ON "taggings"."taggable_id" = "nodes"."id" AND "taggings"."taggable_type" = 'Node' AND "taggings"."context" = 'user_tags' INNER JOIN "tags" ON "tags"."id" = "taggings"."tag_id" GROUP BY "nodes"."id") pg_search_93dd21271636bd02b16bc3 ON pg_search_93dd21271636bd02b16bc3.id = "nodes"."id" WHERE (((to_tsvector('english', coalesce("nodes"."name"::text, '')) || to_tsvector('english', coalesce("nodes"."user_id"::text, '')) || to_tsvector('english', coalesce("nodes"."circa"::text, '')) || to_tsvector('english', coalesce("nodes"."cached_tagged_user_names"::text, '')) || to_tsvector('english', coalesce("nodes"."cached_user_tag_list"::text, '')) || to_tsvector('english', coalesce(pg_search_121ea89914a721445aee70.pg_search_344e3c62d13849726da22e::text, '')) || to_tsvector('english', coalesce(pg_search_4aab10cdca183dac25f479.pg_search_acfcdbc0b3d3a65f40eab7::text, '')) || to_tsvector('english', coalesce(pg_search_4aab10cdca183dac25f479.pg_search_eeb842708b24d4f7fcf549::text, '')) || to_tsvector('english', coalesce(pg_search_4aab10cdca183dac25f479.pg_search_cb8eb1b84bf18ee1412ffd::text, '')) || to_tsvector('english', coalesce(pg_search_b1bc4c0f44e7f4799d8caf.pg_search_9474fb5b090e75ade5136f::text, '')) || to_tsvector('english', coalesce(pg_search_93dd21271636bd02b16bc3.pg_search_484f77386a5aecc6e01094::text, ''))) @@ (to_tsquery('english', ''' ' || 'abc' || ' ''' || ':*'))))  ORDER BY pg_search_rank DESC, "nodes"."id" ASC
=> [#<Node id: 85, name: "House Fire 2", family_tree_id: 57, user_id: 57, media_id: 228, media_type: "Video", created_at: "2015-05-15 00:20:26", updated_at: "2015-06-08 22:25:49", circa: nil, is_comment: nil, cached_votes_total: 0, cached_votes_score: 0, cached_votes_up: 0, cached_votes_down: 0, cached_weighted_score: 0, cached_weighted_total: 0, cached_weighted_average: 0.0, cached_user_tag_list: "[email protected], [email protected]", cached_num_user_tags: 2, cached_tagged_user_names: ["Daniel Marty", "Marcamus Prime"]>,
 #<Node id: 86, name: "10PP Form Video", family_tree_id: 57, user_id: 57, media_id: 229, media_type: "Video", created_at: "2015-05-15 01:26:28", updated_at: "2015-06-05 21:10:09", circa: nil, is_comment: nil, cached_votes_total: 1, cached_votes_score: 1, cached_votes_up: 1, cached_votes_down: 0, cached_weighted_score: 1, cached_weighted_total: 1, cached_weighted_average: 0.0, cached_user_tag_list: "[email protected]", cached_num_user_tags: 1, cached_tagged_user_names: ["Gerry  Atrick"]>]

What could be the cause of this?

Edit 1

To make things more strange, I thought it was the @ that could be throwing off the prefix search, so I tested it by simply changing the attribute of cached_user_tag_list from [email protected] to [email protected]. Then I searched for dannyk and it returned the right result, but when I searched for danny it still returns []. I have no idea why.

1

There are 1 answers

1
sansarp On

You can turn on :trigram search and specify threshold. It matches arbitrary sub-strings that appear anywhere in the searchable text. Your final code would look like this.

pg_search_scope :node_search, against: [:name, :user_id, :circa, :cached_tagged_user_names, :cached_user_tag_list],
     using: { tsearch: { any_word: true, dictionary: :english, prefix: true}, :trigram => { :threshold => 0.1 }  },
        :associated_against => {
          comments: [:message],
          user: [:first_name, :last_name, :email],
          memberships: [:relation],
          user_tags: [:name]
        }