I have this table relationship like:

certificate belongs_to programs

certificate belongs_to users

And I have a form in the certificate view page to get the keyword params.

I want to search for user name and email by the form.

How can I get this done in controller?

<%= form_tag admin_program_path(@program), :method => :get do %>
  <%= text_field_tag :keyword, params[:keyword], :placeholder => "Search by name or email", :class => "form-control rounded" %>
  <%= submit_tag "Search", :class => "btn btn-outline-secondary" %>
<% end %>

I was trying as below, but it can't work. Please help me.

    @certificates = @program.certificates.includes(:user).order("id DESC")
    if params[:keyword].present?
      keyword = "%#{params[:keyword].strip}%"
      @certificates = @certificates.user.where('name LIKE ? OR email LIKE ?', keyword, keyword)
    end

CodePudding user response：

1: find all users by keyword

2: find by found certificates users' ids

users = User.where('name LIKE ? OR email LIKE ?', keyword, keyword)

@certificates = @certificates.where(user_id: users.pluck(:id))

OR joins the users table and use something like @certificates.where('name LIKE ?', keyword)

CodePudding user response：

You are not querying on the users table properly. You need to join the users table with the certificates table and modify your where statement and use users. on name and email as these attributes belong to the users table. This should work:

@certificates = @program.certificates.joins(:user).includes(:user).order("id DESC")
if params[:keyword].present?
  keyword = "%#{params[:keyword].strip}%"
  @certificates = @certificates.where('users.name LIKE ? OR users.email LIKE ?', keyword, keyword)
end