Complex Arel table query ... Need help

Hello every one.

Sorry : I’m not friend in English and not in Ruby :frowning:
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 … :cry:

Sorry

I Forgot to say that I’m interested only for sets with a size of intersection at least of 10.

It is the raison of
.having(es1[:element_id].count.gt(10) )

Thanks

This forum is not affiliated to the Ruby language, Ruby on Rails framework, nor any Ruby applications discussed here.

| Privacy Policy | Terms of Service | Remote Ruby Jobs