active_model_serializer returning more than one result

Question

I'm trying to return the another_id for a related record. I would just add a has_many and belongs_to relation for each project, but I need to have the user id in order to return the correct results. However, with the code I have below, it returns all of the possible another_ids for the current_user.

If I enter this into psql, it works fine:

WITH RECURSIVE t(id, parent_id, path) AS (
    SELECT thing.id, thing.parent_id, ARRAY[thing.id]
    FROM thing, projects
    WHERE thing.id = 595
  UNION
    SELECT i.id, i.parent_id, i.parent_id || t.path
    FROM thing i
    INNER JOIN t ON t.parent_id = i.id
)
SELECT DISTINCT user_thing.another_id FROM user_thing
INNER JOIN t on t.id = user_thing.thing_id
WHERE t.id = user_thing.thing_id AND user_thing.user_id = 2;

 another_id 
-----------
         52
(1 row)

But if I run the code from the serializer, it returns: [52, 51]:

class ProjectSerializer < ActiveModel::Serializer
  attributes :id, :another_id

  def another_id__sql
    "(WITH RECURSIVE t(id, parent_id, path) AS (
        SELECT thing.id, thing.parent_id, ARRAY[thing.id]
        FROM thing, projects
        WHERE thing.id = projects.thing_id
      UNION
        SELECT i.id, i.parent_id, i.parent_id || t.path
        FROM thing i
        INNER JOIN t ON t.parent_id = i.id
    )
    SELECT DISTINCT user_thing.another_id FROM user_thing
    INNER JOIN t on t.id = user_thing.thing_id
    WHERE t.id = user_thing.thing_id AND user_thing.user_id = #{options[:current_user].id})"
  end
end

class API::V1::ProjectsController < API::APIController
  def index
    render json: Project.all
  end

  private

  def default_serializer_options
    { current_user: @current_user }
  end
end

From what I can gather, I'm not understanding how active_model_serializers serializes more than one record.

I'm using rails 4.2.3 and active_model_serializers 0.8.3. I'm afraid I can't change the schema. Also, it probably doesn't matter, but this is the API for an Ember app.

Thanks in advance. I'm a bit embarrassed that I'm having trouble with this.

Edit:

I should probably mention that this is what my project model looks like:

class Project < ActiveRecord::Base
  belongs_to :thing
  has_many :user_thing, through: :thing

  attr_accessor :another_id

  def set_another_id(user)
    connection = ActiveRecord::Base.connection
    result = connection.execute("(WITH RECURSIVE t(id, parent_id, path) AS (
        SELECT thing.id, thing.parent_id, ARRAY[thing.id]
        FROM thing, projects
        WHERE thing.id = #{thing_id}
      UNION
        SELECT i.id, i.parent_id, i.parent_id || t.path
        FROM thing i
        INNER JOIN t ON t.parent_id = i.id
    )
    SELECT DISTINCT user_thing.another_id FROM user_thing
    INNER JOIN t on t.id = user_thing.thing_id
    WHERE t.id = user_thing.thing_id AND user_thing.user_id = #{user.id})")

    @another_id = result[0]["another_id"].to_i
  end
end

And this is the show action in the controller:

def show
  @project = Project.find(params[:id])
  @project.set_another_id(@current_user)
  render json: @project
end

The show action does return the correct id.

Also, I know what I have is incorrect. The thing is that I can't just use the activerecord associations, because it depends on that session's current user.

Edit 2:

I thought I was able to get it to work if I just rendered it using: render json: Project.all.to_json, and got rid of the another_id__sql method in the serializer. That does work if it does have another_id. However, if that's nil, I get the error: "NoMethodError in API::V1::ProjectsController#index undefined method []' for nil:NilClass". It looks like this is a possible bug in 0.8, so I'll either have to ask another Stack Overflow question, or I'll have to see if I can upgrade theactive_model_serializers` gem. I was wrong! See my answer below.

Answer 1

All the DB logic belongs in your model, not in your serializer. The serializers simply state what is supposed to be exposed, but it should not be responsible for computing it. So here, I'd advise to make this another_id a method on your model, which won't solve your issue (as it seems it is more of an SQL issue than anything else), but it will make it so that you don't have a problem with AMS anymore.

Answer 2

Serializers take a record and return a serialized representation suitable for JSON or XML encoding.

They are meant as an alternative to littering your controllers with this:

render json: @users, except: [:foo, :bar, :baz], include: [..........]

And the mental flatulence that is jbuilder.

SQL queries and scopes instead belong in your models.

You can set the serializer by using the each_serializer option. But in this case it will not do you much good the objects you serialize must at least implement the base methods for a serializable model.

So you need to re-write your query so that it returns a collection or array of records.

see:

• http://apidock.com/rails/ActiveRecord/Base/find_by_sql/class
• https://github.com/rails-api/active_model_serializers/blob/master/lib/active_model_serializers/model.rb
• https://github.com/rails-api/active_model_serializers

Answer 3

Got it! It appears that I needed one more method in the serializer:

project_serializer.rb

def another_id
  object.another_id
end

def another_id__sql
  # sql from before
end

I'm not 100% sure why this works, but I had noticed that, if I left out the another_id__sql, I would get the error column.projects.another_id does not exist. So, I'm guessing that the another_id__sql is called when it's returning an array, but uses the another_id method when the object is a single project record.

I'd still love to hear better ways to do this!