-1

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).

bazfer
  • 23
  • 8

1 Answers1

0

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]]
Tom Copeland
  • 1,221
  • 10
  • 10
  • Tried this approach in a variety of ways, didn't work out for me. Didn't specify the DB I'm using, which is Postgres, and it doesn't like the alias for the returned value from the Case When. Also, applied the approach to this more simple query and the order by is not kicking in: SELECT id, total FROM orders ORDER BY CASE WHEN EXISTS (SELECT id, total FROM orders WHERE orders.total > 100000) THEN true ELSE false END – bazfer Dec 19 '17 at 17:24
  • I am now looking at dynamic query generation to solve this fun problem. Thanks for the assist, though. – bazfer Dec 19 '17 at 17:25
  • @bazfer I'm suprised that case/when construct isn't working for you... I'm using Postgres (9.6) too, but I may have misunderstood the schema. I added the schema I was using, corrections welcome. – Tom Copeland Dec 19 '17 at 18:28
  • it works! Just needed to add 'ORDER BY pinned DESC'. Thanks a trillion. – bazfer Dec 19 '17 at 20:08
  • Ohh good call you're right @bazfer I forgot about the ORDER clause. Sounds good! – Tom Copeland Dec 19 '17 at 20:48