On Mar 18, 2:34 am, "[email protected]" <[email protected]>
wrote:
> Hi,
>
> Can someone advise me, how to handle the sorting properly? Right now,
> I'm doing it in a very dumb way.
>
> My scheme is:
>
> create_table(:checklists) do
> primary_key :id
> String :title, null: false
> DateTime :updated, null: false
> end
>
> create_table(:tags) do
> primary_key :id
> String :name, null: false, index: true
> end
>
> create_table(:checklists_tags) do
> primary_key :id
> foreign_key :tag_id, :tags
> foreign_key :checklist_id, :checklists
> end
>
> Models:
>
> class Checklist < Sequel::Model
> many_to_many :tags
> end
> class Tag < Sequel::Model
> many_to_many :checklists
> end
>
> I need to search in tag names and print the result of matching
> 'checklists' in a descending order. The naive approach would be
> something like:
>
> Checklist.order(:updated.desc).each {|i|
> p i.title if i.tags_dataset.filter(:name.like('%mike%')).first
> }
>
> But this is obviously the slowest method. How to do the sorting and
> searching together (using the block _only_ for printing the result)?
You could use eager_graph:
Checklist.eager_graph(:tags).
order(:checklists__updated.desc).
filter(:tags__name.like('%mike%'))
but I wouldn't do that unless you are going to do something with the
related tags. If you just want checklists that have such tags and
don't care about the tags themselves, a subselect should work:
Checklist.order(:updated.desc).
filter(:id=>DB[:checklists_tags].
select(:checklist_id).
join(:tags, :id=>:tag_id).
filter(:tags__name.like('%mike%')))
Note that the LIKE '%mike%' filter call won't use a database index in
most cases, so it'll do a full scan of the tags table.
Jeremy
--
You received this message because you are subscribed to the Google Groups
"sequel-talk" group.
To post to this group, send email to [email protected].
To unsubscribe from this group, send email to
[email protected].
For more options, visit this group at
http://groups.google.com/group/sequel-talk?hl=en.
