Rails/SQL help: three tables, select and sort by presence of another record

Question

Using the ActsAsTaggableOn gem, taggable object is Template. Plus these associations:

class Template
  acts_as_taggable
  has_many :template_designs
end

class Pins
  belongs_to :template
  belongs_to :tag
end

class Tags
  has_many :taggings
end

Goal: a collection of templates ready to be paginated where the user selects a tag, we find all templates matching that tag, and sort them by whether the same tag and template exist in pins, with trues on top.

EDIT - Simplifying and paraphrasing.

Given that a Template is tagged with Tags, and those Tags may or may not have a Pin, I need to select all Templates with X tag, and sort them whether that Tag has a Pin (boolean sort, trues on top).

Answer 1

One approach is to use outer joins together with a CASE WHEN EXISTS expression:

select templates.*, case when exists 
  (select pins.id 
    from pins 
    where pins.tag_id = tags.id 
    and pins.template_id = templates.id) then true else false end as pinned 
  from templates, taggings, tags, pins 
  where templates.id = taggings.template_id 
  and taggings.tag_id = tags.id 
  and tags.name = 'funny';

Here's an Active Record syntax for this:

>> Template.left_outer_joins(:tags)
.where(tags: {name: 'funny'})
.select(:id, :name, 
"case when exists 
  (select pins.id from pins 
  where pins.tag_id = tags.id 
  and pins.template_id = templates.id)
  then true else false end as pinned")
.map {|t| [t.id, t.name, t.pinned] }
[... sql output ...]
=> [[1, "template1", true], [2, "template2", false]]