Disclaimer: I’m fully aware that the below is more of an SQL question than a Sequel question, but I hope maybe Sequel (or its models/plugins) has some nice interface to the below that does not require the ‘first formulate this in SQL, then rewrite the query in Sequel’ route. Also, I’m on a plane (to FOSDEM, as it happens), and can’t do a proper search. (This is the place where you disregard the fact that I’ll be able to search by the time I actually bother you with this email…)
I have three tables: labels, signatures and their join table,
labels_signatures. I want to get a random signature based on the
provided labels; for example, if I provide ‘en’ and ‘tech’, a random
signature might be the one at the end of this email (i.e., one in
English *and* rather vague to non-tech people).
My current approach is the below – short, but definitely inefficient:
module Signore class Signature < Sequel::Model
many_to_many :labels
def self.find_random_by_labels labels
sigs = labels.empty? ? all : labels.map { |label| Label[:name =>
label].signatures rescue [] }.inject(:&)
sigs.sort_by { rand }.first
end
end end
What I would like to do is to offload the selection of the signatures
to the database, and I’m a bit lost how to elegantly do this.
Note that *all* labels must apply (as opposed to ‘any label’), so
‘…AND label.name IN ('en', 'tech')’-based SQL queries won’t cut it.
— Piotr Szotkowski
--
Good pings come in small packets.
[DrYoung]
signature.asc
Description: Digital signature
