Hello every one.

Sorry : I’m not friend in English and not in Ruby

I failed to build a complex Arel query.

I have 3 tables : One is a “Set” table, One is a “Element” table, and one “elements_sets” is the join table for the relation. I need only these join table for my Arel query :

mysql> describe elements_sets;

±----------------±-----±-----±----±--------±------+

| Field | Type | Null | Key | Default | Extra |

±----------------±-----±-----±----±--------±------+

| element_id | int | NO | PRI | NULL | |

| set_id | int | NO | PRI | NULL | |

±----------------±-----±-----±----±--------±------+

For a given set , les say my_set_id = 33

I want to find the 10 most closer other sets ids in this tables.

I have 3 definitions of my metric :

proximity : size of the intersection of (my_set and a_set) / size of the union of (my_set and a_set)

inclusion of my_set : size of the intersection / size of my_set

inclusion of a_set : size of the intersection / size of a_set

I was able to construct the arel query for the second one, because the denominator is a constant,

and I can order the result based on the size of the intersection.

Here is my query :

es1 = Arel::Table.new :elements_sets

es2 = Arel::Table.new :elements_sets

my_set_id = 33 # for example.

query = es1

.project( :set_id, es1[:element_id].count )

.where(

es1[ :set_id ].not_eq( my_set_id )

.and( es1[:element_id].in(

es2.project(:element_id).where(es2[:set_id].eq( my_set_id ) )))

)

.group( es1[:set_id] )

.having(es1[:element_id].count.gt(10) )

.order(es1[:element_id].count.desc)

.take(10)

Maybe this query, for the second metric, is not optimal, but it’s work.

For the first and last metric, I have no idea how to build the query, because the denominator depend of the size of each “a_set”.

I need to write an expression in the order clause, like

.order( “size of the intersection” / “size of the union” )

Thanks for help me …