Is there a way to use FooQuery write self-defined where clause? e.g. where universities.name &@~ 'foo'

[zw963]

I check the document, but never found how to write where like this:

where universities.name &@~ 'foo'

I done my job use Avram::QueryBuilder, like following:

class Universities::Index < BrowserAction
  get "/universities" do
    q = params.get?(:q)
    is_985 = params.get?(:is_985)
    is_211 = params.get?(:is_211)
    is_good = params.get?(:is_good)

    builder = Avram::QueryBuilder.new(table: :universities)
    unless q.blank?
      builder = builder.where(Avram::Where::Raw.new("(universities.name &@~ ?", q))
      builder = builder.or do |or|
        or.where(Avram::Where::Raw.new("universities.description &@~ ?)", q))
      end
    end

    results = UniversityQuery.new
    results.query = builder

    unless is_985.blank?
      results = results.is_985(true)
    end

    pp! results.to_sql

    pages, universities = paginate(results.id.desc_order, per_page: 50)

    html IndexPage, universities: universities, pages: pages
  end
end

It works!

the above output of to_sql like this:

 ["SELECT * FROM universities WHERE (universities.name &@~ '大学' OR universities.description &@~ '大学') AND universities.is_985 = $1",  "true"]

Can i do same thing just use UniversityQuery instead? i can't find the document for how write self-defined where condition.

Thanks.

[jwoertink]

You need to use a raw where query for this.

query = UniversityQuery.new

if q = params.get?(:q).presence
  query = query.where("name &@~ ?", q).or(&.where("description &@~ ?", q))
end

pp! query.to_prepared_sql

I haven't seen the &@~ query before. What do you call this?

[zw963]

query.where("name &@~ ?", q).or(&.where("description &@~ ?", q))

Hi, i read through the document, not found the usage of above where raw form, could you please point me out where it is?

I haven't seen the &@~ query before. What do you call this?

https://pgroonga.github.io/reference/operators/query-v2.html

It is a full-text search engine that supports multi-byte languages. performance is better, also supports AND, OR, NOT(-), with parenthesis, it even support the regexp engine which same as latest Ruby(optional).

BTW, i propose to support this index type in this issue

[jwoertink]

could you please point me out where it is?

It's not documented, but I opened an issue luckyframework/website#1320

Ah, so that's a special plugin that adds that syntax. 👍