ActiveRecord::StatementInvalid - PG::UndefinedColumn: ERROR: column. For optional association. #427
Description
I'm unable to retrieve data from the Business table based on the optional User association.
Here are the full logs of the application that contains the Error message.
13:58:15 web.1 | Started GET "/en/admin/businesses.json?draw=2&columns%5B0%5D%5Bdata%5D=name&columns%5B0%5D%5Bname%5D=&columns%5B0%5D%5Bsearchable%5D=true&columns%5B0%5D%5Borderable%5D=true&columns%5B0%5D%5Bsearch%5D%5Bvalue%5D=&columns%5B0%5D%5Bsearch%5D%5Bregex%5D=false&columns%5B1%5D%5Bdata%5D=cities&columns%5B1%5D%5Bname%5D=&columns%5B1%5D%5Bsearchable%5D=true&columns%5B1%5D%5Borderable%5D=true&columns%5B1%5D%5Bsearch%5D%5Bvalue%5D=&columns%5B1%5D%5Bsearch%5D%5Bregex%5D=false&columns%5B2%5D%5Bdata%5D=categories&columns%5B2%5D%5Bname%5D=&columns%5B2%5D%5Bsearchable%5D=true&columns%5B2%5D%5Borderable%5D=true&columns%5B2%5D%5Bsearch%5D%5Bvalue%5D=&columns%5B2%5D%5Bsearch%5D%5Bregex%5D=false&columns%5B3%5D%5Bdata%5D=user&columns%5B3%5D%5Bname%5D=&columns%5B3%5D%5Bsearchable%5D=true&columns%5B3%5D%5Borderable%5D=true&columns%5B3%5D%5Bsearch%5D%5Bvalue%5D=&columns%5B3%5D%5Bsearch%5D%5Bregex%5D=false&columns%5B4%5D%5Bdata%5D=created_at&columns%5B4%5D%5Bname%5D=&columns%5B4%5D%5Bsearchable%5D=true&columns%5B4%5D%5Borderable%5D=true&columns%5B4%5D%5Bsearch%5D%5Bvalue%5D=&columns%5B4%5D%5Bsearch%5D%5Bregex%5D=false&columns%5B5%5D%5Bdata%5D=updated_at&columns%5B5%5D%5Bname%5D=&columns%5B5%5D%5Bsearchable%5D=true&columns%5B5%5D%5Borderable%5D=true&columns%5B5%5D%5Bsearch%5D%5Bvalue%5D=&columns%5B5%5D%5Bsearch%5D%5Bregex%5D=false&columns%5B6%5D%5Bdata%5D=actions&columns%5B6%5D%5Bname%5D=&columns%5B6%5D%5Bsearchable%5D=true&columns%5B6%5D%5Borderable%5D=false&columns%5B6%5D%5Bsearch%5D%5Bvalue%5D=&columns%5B6%5D%5Bsearch%5D%5Bregex%5D=false&columns%5B7%5D%5Bdata%5D=disabled&columns%5B7%5D%5Bname%5D=&columns%5B7%5D%5Bsearchable%5D=true&columns%5B7%5D%5Borderable%5D=true&columns%5B7%5D%5Bsearch%5D%5Bvalue%5D=&columns%5B7%5D%5Bsearch%5D%5Bregex%5D=false&columns%5B8%5D%5Bdata%5D=approved&columns%5B8%5D%5Bname%5D=&columns%5B8%5D%5Bsearchable%5D=true&columns%5B8%5D%5Borderable%5D=true&columns%5B8%5D%5Bsearch%5D%5Bvalue%5D=&columns%5B8%5D%5Bsearch%5D%5Bregex%5D=false&columns%5B9%5D%5Bdata%5D=flagged&columns%5B9%5D%5Bname%5D=&columns%5B9%5D%5Bsearchable%5D=true&columns%5B9%5D%5Borderable%5D=true&columns%5B9%5D%5Bsearch%5D%5Bvalue%5D=&columns%5B9%5D%5Bsearch%5D%5Bregex%5D=false&columns%5B10%5D%5Bdata%5D=verified&columns%5B10%5D%5Bname%5D=&columns%5B10%5D%5Bsearchable%5D=true&columns%5B10%5D%5Borderable%5D=true&columns%5B10%5D%5Bsearch%5D%5Bvalue%5D=true&columns%5B10%5D%5Bsearch%5D%5Bregex%5D=false&order%5B0%5D%5Bcolumn%5D=5&order%5B0%5D%5Bdir%5D=desc&order%5B0%5D%5Bname%5D=&start=0&length=30&search%5Bvalue%5D=&search%5Bregex%5D=false&approved=&flagged=&verified=true&_=1724144288550" for ::1 at 2024-08-20 13:58:15 +0500 13:58:15 web.1 | Processing by Admin::BusinessesController#index as JSON 13:58:15 web.1 | Parameters: {"draw"=>"2", "columns"=>{"0"=>{"data"=>"name", "name"=>"", "searchable"=>"true", "orderable"=>"true", "search"=>{"value"=>"", "regex"=>"false"}}, "1"=>{"data"=>"cities", "name"=>"", "searchable"=>"true", "orderable"=>"true", "search"=>{"value"=>"", "regex"=>"false"}}, "2"=>{"data"=>"categories", "name"=>"", "searchable"=>"true", "orderable"=>"true", "search"=>{"value"=>"", "regex"=>"false"}}, "3"=>{"data"=>"user", "name"=>"", "searchable"=>"true", "orderable"=>"true", "search"=>{"value"=>"", "regex"=>"false"}}, "4"=>{"data"=>"created_at", "name"=>"", "searchable"=>"true", "orderable"=>"true", "search"=>{"value"=>"", "regex"=>"false"}}, "5"=>{"data"=>"updated_at", "name"=>"", "searchable"=>"true", "orderable"=>"true", "search"=>{"value"=>"", "regex"=>"false"}}, "6"=>{"data"=>"actions", "name"=>"", "searchable"=>"true", "orderable"=>"false", "search"=>{"value"=>"", "regex"=>"false"}}, "7"=>{"data"=>"disabled", "name"=>"", "searchable"=>"true", "orderable"=>"true", "search"=>{"value"=>"", "regex"=>"false"}}, "8"=>{"data"=>"approved", "name"=>"", "searchable"=>"true", "orderable"=>"true", "search"=>{"value"=>"", "regex"=>"false"}}, "9"=>{"data"=>"flagged", "name"=>"", "searchable"=>"true", "orderable"=>"true", "search"=>{"value"=>"", "regex"=>"false"}}, "10"=>{"data"=>"verified", "name"=>"", "searchable"=>"true", "orderable"=>"true", "search"=>{"value"=>"true", "regex"=>"false"}}}, "order"=>{"0"=>{"column"=>"5", "dir"=>"desc", "name"=>""}}, "start"=>"0", "length"=>"30", "search"=>{"value"=>"", "regex"=>"false"}, "approved"=>"", "flagged"=>"", "verified"=>"true", "_"=>"1724144288550", "locale"=>"en"} 13:58:15 web.1 | City Load (0.3ms) SELECT "cities"."id", "cities"."country_id", "cities"."latitude", "cities"."longitude", "cities"."banner_file_name", "cities"."banner_content_type", "cities"."banner_file_size", "cities"."banner_updated_at", "cities"."slug", "cities"."disabled" FROM "cities" WHERE "cities"."id" = $1 LIMIT $2 [["id", 13], ["LIMIT", 1]] 13:58:15 web.1 | ↳ app/controllers/concerns/localise/user_city.rb:35:in get_city_cookie'
13:58:15 web.1 | Country Load (0.3ms) SELECT "countries"."id", "countries"."latitude", "countries"."longitude", "countries"."created_at", "countries"."updated_at", "countries"."disabled" FROM "countries" WHERE "countries"."id" = $1 LIMIT $2 [["id", 2], ["LIMIT", 1]]
13:58:15 web.1 | ↳ app/models/city.rb:73:in enabled?' 13:58:15 web.1 | Admin Load (0.3ms) SELECT "admins".* FROM "admins" WHERE "admins"."id" = $1 ORDER BY "admins"."id" ASC LIMIT $2 [["id", 47], ["LIMIT", 1]] Business Count (3.5ms) SELECT COUNT(*) FROM (SELECT DISTINCT "businesses"."id" FROM "businesses" LEFT OUTER JOIN "locations" ON "locations"."owner_type" = $1 AND "locations"."owner_id" = "businesses"."id" LEFT OUTER JOIN "cities" ON "cities"."id" = "locations"."city_id" LEFT OUTER JOIN "city_translations" ON "city_translations"."city_id" = "cities"."id" LEFT OUTER JOIN "business_translations" ON "business_translations"."deleted_at" IS NULL AND "business_translations"."business_id" = "businesses"."id" LEFT OUTER JOIN "users" ON "users"."id" = "businesses"."user_id" LEFT OUTER JOIN "business_services" ON "business_services"."deleted_at" IS NULL AND "business_services"."business_id" = "businesses"."id" LEFT OUTER JOIN "services" ON "services"."id" = "business_services"."service_id" LEFT OUTER JOIN "sub_categories" ON "sub_categories"."id" = "services"."sub_category_id" LEFT OUTER JOIN "categories" ON "categories"."id" = "sub_categories"."category_id" LEFT OUTER JOIN "category_translations" ON "category_translations"."category_id" = "categories"."id" WHERE "cities"."id" IS NULL AND "businesses"."user_id" IS NOT NULL) subquery_for_count [["owner_type", "Business"]] | Business Count (2.6ms) SELECT COUNT(*) FROM (SELECT DISTINCT "businesses"."id" FROM "businesses" LEFT OUTER JOIN "locations" ON "locations"."owner_type" = $1 AND "locations"."owner_id" = "businesses"."id" LEFT OUTER JOIN "cities" ON "cities"."id" = "locations"."city_id" LEFT OUTER JOIN "city_translations" ON "city_translations"."city_id" = "cities"."id" LEFT OUTER JOIN "business_translations" ON "business_translations"."deleted_at" IS NULL AND "business_translations"."business_id" = "businesses"."id" LEFT OUTER JOIN "users" ON "users"."id" = "businesses"."user_id" LEFT OUTER JOIN "business_services" ON "business_services"."deleted_at" IS NULL AND "business_services"."business_id" = "businesses"."id" LEFT OUTER JOIN "services" ON "services"."id" = "business_services"."service_id" LEFT OUTER JOIN "sub_categories" ON "sub_categories"."id" = "services"."sub_category_id" LEFT OUTER JOIN "categories" ON "categories"."id" = "sub_categories"."category_id" LEFT OUTER JOIN "category_translations" ON "category_translations"."category_id" = "categories"."id" WHERE "cities"."id" IS NULL AND "businesses"."user_id" IS NOT NULL AND CAST("businesses"."user" AS VARCHAR) ILIKE '') subquery_for_count [["owner_type", "Business"]] 13:58:15 web.1 | ↳ app/controllers/admin/businesses_controller.rb:15:in block (2 levels) in index'
13:58:15 web.1 | Completed 500 in 37ms (ActiveRecord: 7.0ms | Allocations: 24032)
13:58:15 web.1 |
13:58:15 web.1 | ActiveRecord::StatementInvalid - PG::UndefinedColumn: ERROR: column businesses.user does not exist
13:58:15 web.1 | LINE 1: ...L AND "businesses"."user_id" IS NOT NULL AND CAST("businesse...
13:58:15 web.1 | ^:
13:58:15 web.1 | app/controllers/admin/businesses_controller.rb:15:in block (2 levels) in index' 13:58:15 web.1 | app/controllers/admin/businesses_controller.rb:13:in index'
`
Below are the full details of the code implementation. ⬇️
Here is the relation of Business and User models.
class Business < ApplicationRecord
belongs_to :user, optional: true
end
class User < ApplicationRecord
has_many :businesses
end
Here is the controller.
class Admin::BusinessesController < Admin::BaseController
include EmailHelper
after_action :verify_authorized
def index
authorize [:admin, Business]
@datatable = BusinessDatatable.new(params, view_context: view_context)
respond_to do |format|
format.html
format.json { render json: @datatable }
end
end
end
Here is the policy.
class Admin::BusinessPolicy < ApplicationPolicy
class Scope < Scope
def index?
!@user.moderator?
end
def resolve
#using includes to businesses with no location are shown
scope.includes(
locations: :city
).where(
cities: {
id: @user.accessible_city_ids
}
)
end
end
end
Here is the business_datatable.rb file.
class BusinessDatatable < AjaxDatatablesRails::ActiveRecord
extend Forwardable
def_delegators :@view, :current_admin, :session
def initialize(params, opts = {})
@view = opts[:view_context]
super
end
def view_columns
@view_columns ||= {
id: { source: "Business.id", cond: :eq },
name: { source: "Business::Translation.name", cond: :like },
cities: { source: "City::Translation.name", cond: :like },
categories: { source: "Category::Translation.name", cond: :like },
user: { source: "Business.user", cond: :like, searchable: true, orderable: true },
updated_at: { source: "Business.updated_at", cond: :like },
created_at: { source: "Business.created_at", cond: :like },
disabled: { source: "Business.disabled", cond: :eq },
approved: { source: "Business.approved", cond: :eq },
flagged: { source: "Business.flagged", cond: :eq },
verified: { source: "Business.user", cond: :eq },
role: { source: "Admin.role", cond: :eq },
actions: { source: "Business.id", cond: :null_value }
}
end
def data
records.map do |record|
{
id: record.id,
name: record.name,
cities: record.cities.map { |city| { id: city.id, name: city.name } }.uniq.to_json.html_safe,
categories: record.categories.distinct.map { |category| { id: category.id, name: category.name } }.to_json.html_safe,
user: record.user.present? ? record.user.as_json(only: [:id, :name]).to_json.html_safe : nil,
updated_at: record.updated_at,
created_at: record.created_at,
disabled: record.disabled?,
approved: record.approved?,
paid: record.user && record.user.business_subscription_data.has_key?("subscription") ? true : false,
flagged: record.flagged?,
verified: record.verified? || false,
role: @view.current_admin.role,
actions: "",
}
end
end
private
def get_raw_records
# Extract search values
user_search = params.dig(:columns, "3", :search, :value).to_s
verified_search = params[:verified].to_s
# Initial query setup
query = Admin::BusinessPolicy::Scope.new(current_admin, Business).resolve
if verified_search.present?
if verified_search == "true"
query = query.where.not(user_id: nil) # Businesses that are verified
elsif verified_search == "false"
query = query.where(user_id: nil) # Businesses that are not verified
end
end
# Apply user filter if present
if user_search.present?
query = query.includes(:translations, locations: { city: :translations }, categories: :translations)
.left_joins(:user) # Use left_joins to include records where user may be nil
.where("users.name ILIKE ?", "%#{user_search}%") # Use ILIKE for case-insensitive matching
.references(:location, :city, :translation, :category, :user)
else
query = query.includes(
:translations,
:user,
locations: { city: :translations },
categories: :translations
)
# Filter by country if needed
query = query.joins(locations: :country).where('countries.id' => session[:admin_country_id]) if session[:admin_country_id]
# Select required fields
query = query.select('businesses.*, users.name as user_name, city_translations.name as city_name, category_translations.name as category_name')
end
query
end
end
Here is the businesses.js.erb file.
$("#js-businesses-table").dataTable({
serverSide: true,
bLengthChange: false,
bInfo: false,
order: [[5, "desc"]],
ajax: {
url: $("#js-businesses-table").data("source"),
data: function (d) {
console.log(d);
d.approved = $("#approved_filter").val();
d.flagged = $("#flagged_filter").val();
d.verified = $("#verified_filter").val();
console.log("Approved:", $("#approved_filter").val());
console.log("Flagged:", $("#flagged_filter").val());
console.log("Verified:", $("#verified_filter").val());
},
},
fnRowCallback: function (nRow, aData, iDisplayIndex) {
$(nRow).removeClass("unapproved paid");
if (!aData.approved) {
$(nRow).addClass("unapproved");
}
if (aData.paid) {
$(nRow).addClass("paid");
}
},
columns: [
{
data: "name",
render: function (data, type, row, meta) {
return (
'<a href="/admin/businesses/' + row.id + '/edit">' + data + "</a>"
);
},
},
{
data: "cities",
render: function (data, type, row, meta) {
let newData = JSON.parse(data);
let cities = newData
.map(function (city, i) {
return (
'<a href="/admin/cities/' +
city.id +
'/edit">' +
city.name +
"</a>" +
(i != newData.length - 1 ? ", " : "")
);
})
.join("");
return cities;
},
defaultContent: "-",
},
{
data: "categories",
render: function (data, type, row, meta) {
let newData = JSON.parse(data);
categories = [];
$(newData).map(function (i) {
categories +=
'<a href="/admin/categories/' +
newData[i].id +
'/edit">' +
newData[i].name +
"</a>" +
(i != newData.length - 1 ? ", " : "") +
"";
});
return categories;
},
},
{
data: "user",
render: function (data, type, row, meta) {
if (!data) {
return "no vendor";
}
if (typeof data === "string") {
try {
data = JSON.parse(data);
} catch (e) {
console.error("Error parsing JSON:", e);
return "no vendor";
}
}
if (data && data.id && data.name) {
return (
'<a href="/admin/users/' + data.id + '/edit">' + data.name + "</a>"
);
} else {
return "no vendor";
}
},
sortable: true,
orderable: true,
},
{
data: "created_at",
render: function (data, type, row, meta) {
return moment(data).format("DD/MM/YY");
},
},
{
data: "updated_at",
render: function (data, type, row, meta) {
return moment(data).format("DD/MM/YY");
},
},
{
data: "actions",
sortable: false,
render: function (data, type, row, meta) {
return (
'<a class="tooltip" title="Edit" href="/admin/businesses/' +
row.id +
'/edit"><%= image_tag("icons/pencil.svg", class: "icon icon--dark icon--small") %></a> ' +
(data.role === "superadmin"
? '<a class="tooltip" title="Delete" rel="nofollow" data-method="delete" href="/admin/businesses/' +
row.id +
'" data-confirm="Are you sure?"><%= image_tag("icons/trash.svg", class: "icon icon--dark icon--small") %></a> '
: "") +
'<a class="tooltip" title="Public profile" target="_blank" href="/businesses/' +
row.id +
'"><%= image_tag("icons/eye.svg", class: "icon icon--dark icon--small") %></a> ' +
(row.disabled
? '<a class="tooltip" title="Enable" data-method="put" href="/admin/businesses/' +
row.id +
'/enable"><%= image_tag("icons/verified.svg", class: "icon icon--dark icon--small") %></a>'
: '<a class="tooltip" title="Disable" data-method="put" href="/admin/businesses/' +
row.id +
'/disable"><%= image_tag("icons/close-small.svg", class: "icon icon--dark icon--small") %></a>')
);
},
},
{
data: "disabled",
visible: false,
},
{
data: "approved",
visible: false,
sortable: true,
},
{
data: "flagged",
visible: false,
},
{
data: "verified",
visible: false,
},
],
dom: "ritp",
pageLength: 30,
initComplete: function () {
var table = this;
// Select filters
$(".js-select-filter").each(function () {
var $filterSelect = $(this);
var columnIndex = +$filterSelect.data("column");
var column = table.api().column(columnIndex);
// Filter the column when the user picks an option
$filterSelect.on("change", function () {
var val = $filterSelect.val();
if (val === "true" || val === "false") {
val = val === "true";
}
column.search(val ? val : "", false, false).draw();
});
// Add all possible options to the select
$filterSelect.append(
'<option value="">' + column.header().innerHTML + "</option>"
);
var entries = column
.data()
.unique()
.sort()
.map(function (d, j) {
if (typeof d === "string") {
try {
return JSON.parse(d);
} catch (e) {
return [];
}
} else {
return d || [];
}
});
entries = _.uniqBy(_.flattenDeep(entries), "id").filter(function (value) {
return value && value.id !== undefined;
});
entries.forEach(function (entry) {
if (entry.name === true || entry.name === false) {
entry.name = entry.name === true ? "Yes" : "No";
}
$filterSelect.append(
'<option value="' + entry.name + '">' + entry.name + "</option>"
);
});
// Add options for boolean columns
if (columnIndex === 8 || columnIndex === 9 || columnIndex === 10) {
$filterSelect.append('<option value="true">True</option>');
$filterSelect.append('<option value="false">False</option>');
}
});
// Text filters
$(".js-text-filter").each(function () {
var $filterInput = $(this);
var columnIndex = +$filterInput.data("column");
var column = table.api().column(columnIndex);
$filterInput.on("keyup change", function () {
if (column.search() !== this.value) {
column.search(this.value).draw();
}
});
});
},
});
$("#js-businesses-table").on("draw.dt", function () {
$(".tooltip").tooltipster({
delay: 0,
animationDuration: 100,
});
});