[SOLVED] How to explicitly specify PARTITION in ActiveRecord SELECT query?

How to explicitly specify PARTITION in ActiveRecord SELECT query?

I have a huge partitioned table and I know partition name in advance before executing SELECT query.

In vanilla SQL the syntax is SELECT * FROM objects PARTITION (p1)

My current workaround seems to be working but looks kind of hacky:

active_record_relation.to_sql.gsub('FROM `objects`', "FROM `objects` PARTITION(#{explicit_partition_name})")

I'm wondering if there is a more idiomatic ActiveRecord solution to this?

Just to be clear, I'm aware of Partition Pruning but I can't use it, because partition hashing/finding logic is an external component/system.

The database is MySQL if that's important.

Solution

So just because I stumbled across this post you can construct that in a sideways manner

partition_name = 'p1'
active_record_relation.from(
  Arel::Nodes::UnaryOperation.new(
    active_record_relation.arel_table.name, 
    Arel::Nodes::NamedFunction.new('PARTITION',[Arel.sql(partition_name)])
  )
)

This will produce the desired SQL

SELECT 
  objects.* 
FROM  
  objects PARTITION(p1)

Full Arel Example:

table = Arel::Table.new('objects')
table 
  .project(table[Arel.star])
  .from(Arel::Nodes::UnaryOperation.new(
          table.name, 
          Arel::Nodes::NamedFunction.new('PARTITION',[Arel.sql('p1')])))
  .to_sql