# Complex Arel table query ... Need help

**URL:** <https://www.ruby-forum.com/t/complex-arel-table-query-need-help/262206>\
**Category:** Development\
**Created:** [December 10, 2021, 5:44pm UTC](https://www.ruby-forum.com/t/complex-arel-table-query-need-help/262206 "2021-12-10T17:44:54Z")\
**Posts on this page:** 2\
**Page:** 1

<div class="post-metadata">

**Author:** ![empedocle](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/e/898d66/32.png) [@empedocle](https://www.ruby-forum.com/u/empedocle)\
**Post date:** [December 10, 2021, 5:44pm UTC](https://www.ruby-forum.com/t/complex-arel-table-query-need-help/262206/1 "2021-12-10T17:44:54Z")

</div>

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 … 😢

---

<div class="post-metadata">

**Author:** ![empedocle](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/e/898d66/32.png) [@empedocle](https://www.ruby-forum.com/u/empedocle)\
**Post date:** [December 10, 2021, 5:51pm UTC](https://www.ruby-forum.com/t/complex-arel-table-query-need-help/262206/2 "2021-12-10T17:51:05Z")

</div>

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