loading...

acts-as-taggable-on causing SQL errors when ordered by relation

drbragg profile image Drew Bragg ・2 min read

I’m having a weird issue with acts-as-taggable-on when using tagged_with and the any flag.

My model looks something like this:

class Document < ApplicationRecord
  ...
  belongs_to :phase, optional: true
  ...

  scope :search_by_drug, ->(drug) { tagged_with(drug, on: :drug) if drug.present? }
  scope :search_by_area, ->(area) { tagged_with(area, on: :area) if area.present? }
  scope :search_by_type, ->(types) { tagged_with(types, on: :doc_type, any: true) if types.present? }
  scope :search_by_tags, ->(tags) { tagged_with(tags, on: :tags, any: true) if tags.present? }
  ...

  acts_as_taggable_on :tags, :drug, :area, :doc_type
  ...
end

and in my controller this is where I'm querying:

class DocumentsController < ApplicationController
  include Pagy::Backend
  ...

  # GET /documents
  def index
    @pagy, @documents = pagy(
      Document.includes(:phase, :taggings)
              .search_by_drug(params[:drug])
              .search_by_area(params[:area])
              .search_by_type(params[:types])
              .search_by_tags(params[:tags])
              .order(sort_order),
      items: 5
    )
  end
  ...

  private

  def sort_by
    return 'phases.name' if params[:sort] == 'phase'

    @sort_by = params[:sort] || 'date'
  end

  def order
    @order = params[:order] || 'asc'
  end

  def sort_order
    "#{sort_by} #{order}"
  end
  ...
end

When I'm searching with tags or by types and I try to order the phase column I’m thrown an error that looks like this:

ERROR: for SELECT DISTINCT, ORDER BY expressions must appear in select list
LINE 1: ...) AND "taggings"."context" = 'doc_type') ORDER BY phases.nam...
.

I’ve done a bunch of searching but the closest issues I could come up with was an old issue on the acts-as-taggable-on github but it was closed because (according to them) they had merged a PR that fixed the issue and there’s nothing new in their issues or SO.

Can anyone point me in the right direction?

Posted on by:

drbragg profile

Drew Bragg

@drbragg

Full Stack Dev && Single Dad && Board Game Geek && Hockey Player

Discussion

pic
Editor guide
 

I've run into this too. I can't remember if I found a solution or just went in another direction.

I can't offer a lot of insight now but I'll give this some thought and come back to you if I have time to look into it.

Good luck!

 

Thanks, Ben, I appreciate it!

Ordering by phase works fine if I'm searching by drug or area, but the moment either types or tags is included I get the error. It's giving me a fun headache

 

Still haven't figured this out. Might be opening up an issue on their GitHub if I can figure out a work around.