ActiveRecord::StatementInvalid - PG::UndefinedColumn: ERROR: column. For optional association.

[abdulbasitkhandeveloper]

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:inget_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:inblock (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:inindex'

`

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,
  });
});

[abdulbasitkhandeveloper]

Waiting for the response. 👀