searching on association table (not foreign key) causes crashes

[nekapoor]

We have and parents table and a students table (A parent has many students). There's a parent_id field on the students table.

On the parents table, there is also a separate uid field which is a longer unique field.

ANd in the students datatable, I display the parent uid field with:

    col :parent_uid, label: 'Parent uid', do |student|
      student.parent.uid
    end

but when I search in this column, the app will crash often. The query is something like select * from parents where id in <all the ids>. You can see an image for what it looks like.

I'm not sure if I've explained this well, but happy to clarify anything. Any thoughts on this?

Thank you!

[matt-riemer]

Hey there! :)

Yep, looking at the screenshot here, I think the problem is actually with sorting by the parent.uid column. The effective_datatables tries to sort without doing any internal joins or changing your collection at all.

The quick fix is to just to disable sorting, by going col(:parent_uid, sort: false)

A second fix, would be to use the join syntax. This would change your collection to be:

collection do
  Student.all.joins(:parent)
end

and then define your column:

col('parent.uid')

And it'll sort with a much better SQL query.

If you don't want to change the whole collection, you could only perform this join when sorting on this column, something like:

col(:parent_uid) do |student|
  student.parent.uid
end.sort do |collection, direction, column, sql_column|
    collection.joins(:parent).order(:parent => :uid, direction)
end

Hope this actually helps! :)