ActiveRecord在where子句不明确的错误中加入投掷Column-Rails 5.1 [英] ActiveRecord joins throwing Column in where clause is ambiguous error - Rails 5.1

问题描述

我正在一个Rails 5.1项目中，该项目需要通过记录字段的值以及存在连接的Category记录(Project具有并且属于许多Category).

I'm working on a Rails 5.1 project which required the indexing and searching of Project records by values of the record's fields, and by the existence of a connected Category record (Project has and belongs to many Category).

我有一个SearchController和一个search动作，该动作包含一个收集搜索参数的表单.在表单中，我有一个用于名称参数的输入和一个用于类别的选择.除非搜索返回0个结果，否则搜索将成功运行，当有结果要返回时，一切正常.在不应有任何结果的情况下，ActiveRecord会引发错误:

I have a SearchController with a search action, which contains a form collecting search parameters. In the form, I have an input for a name parameter and a select for the category. The search runs successfully unless the search returns 0 results, all works ok when there are results to return. In cases where there should be no results, ActiveRecord throws the error:

Mysql2::Error: Column 'name' in where clause is ambiguous: SELECT COUNT(*) FROM `projects` INNER JOIN `categories_projects` ON `categories_projects`.`project_id` = `projects`.`id` INNER JOIN `categories` ON `categories`.`id` = `categories_projects`.`category_id` WHERE (name LIKE '%something%') AND `categories`.`id` = 1

关于什么可能导致这种情况的任何想法?

Any ideas on what could be causing this?

项目模型

class Project < ApplicationRecord
  has_and_belongs_to_many :categories
end

类别模型

class Category < ApplicationRecord
  has_and_belongs_to_many :projects
end

SearchController

class SearchController < ApplicationController
  def search
    projects = Project.all
    projects = projects.where('name LIKE ?', "%#{params[:name]}%") if params[:name].present?
    projects = projects.joins(:categories).where(categories: { id: params[:category_id] }) if params[:category_id].present?
    @projects = projects
  end
end

路线

Rails.application.routes.draw do
  ...

  get "search", to: "search#search"

  ...
end

查看

<h1>Search</h1>
<%= form_tag(search_path, method: :get) do %>
  <%= label_tag :name %>
  <%= text_field_tag :name, params[:name] %>

  <%= label_tag :category_id %>
  <%= select_tag(:category_id, options_for_select(Category.all.collect {|o| [ o.name, o.id ] }, params[:category_id]), { prompt: 'Select Category'}) %>

  <%= submit_tag "Search" %>
<% end %>

<h2><%= pluralize(@projects.count, "Result") %></h2>
<% @projects.each do |project| %>
  <h3><%= project.name %></h3>
<% end %>

推荐答案

当您连接两个表并且两个表都有一个name列时，您必须告诉数据库您要匹配哪个name列.

When you join two tables and both tables have a name column then you have to tell the database which name column you want to match.

  def search
    @projects = Project.all
    @projects = @projects.where('projects.name LIKE ?', "%#{params[:name]}%") if params[:name].present?
    @projects = @projects.joins(:categories).where(categories: { id: params[:category_id] }) if params[:category_id].present?
  end

请注意LIKE查询中的projects.name.

这篇关于ActiveRecord在where子句不明确的错误中加入投掷Column-Rails 5.1的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！