ActiveRecord::StatementInvalid - PG::UndefinedColumn: ERROR: column. For optional association.
abdulbasitkhandeveloper opened this issue · 1 comments
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,
});
});
Waiting for the response. 👀