I often find myself in situations where I'm fighting against ActiveRecord because there is no easy way to alias some tables.
For example, I'm converting a legacy application. So, there is a 'condition_type' table that actually should be something like 'fields'. Also it can have a parent field and deletion is handled by setting a 'deleted' column, so here is the basic class:
class Field self.table_name = 'condition_type' default_scope where(deleted: false) belongs_to :parent, class: 'Field' end
Okay, first set up is awesome! Now the issue:
I'm implementing the tree view in a lazy way using AJAX, so I only need to check for the existence of children.
The easy way is to iterate over the fields like this:
render json: fields.map {|field| ... has_children: field.children.exists? # Field.has_many :children, class: 'Field', foreign_key: 'parent_id' }
But this means fields.count "select 1 where exists(...)" queries. This works fine, but I'd prefer to have something like this:
field_ids_with_children = Field.find_by_sql(['select id from condition_type f1 where id in (?) and exists(select 1 from condition_type where parent_id=f1.id)', fields.map(&:id)]).map(&:id)
And then have something like "has_children: field_ids_with_children.include? field.id".
But if you're taking enough attention, you'll see that there is a bug in the above query because it is not checking if the fields were deleted.
This would be the correct query (simplified, not my actual case), since the original 'fields' var only contain fields with deleted = false:
select id from condition_type f1 where id in (?) and exists(select 1 from condition_type where deleted=? and parent_id=f1.id)
So, I'd like to propose adding an "alias" or "as" method to ActiveRecord, so that it would work this way:
field_ids_with_children = Field.as(:f1).select(:id).where(id: fields.map(&:id)).where(Field.where('parent_id = f1.id').exists)
Currently I couldn't find any solution to work around this issue. For example, suppose this query:
field_ids_with_children = Field.from(Field.arel_table.alias('f1')).select(:id).where('f1.id' => fields.map(&:id)).where(Field.where('parent_id = f1.id').exists)
This would still raise an error since the default scope will use "condition_type.deleted = 'f'" instead of "f1.deleted = 'f'".
For this particular case, using Field.unescoped.from... would do it, but can you see how it would be useful to add an "alias" or "as" method to ActiveRecord?
Thank you for your patience on reading all of this.
Cheers, Rodrigo.
