On Wednesday, November 14, 2018 at 1:04:11 PM UTC-8, craig buchanan wrote:
>
> I have three tables:
>
> users --< users_entities >-- entities
>
> User model:
>
> many_to_many :entities,
> :class => Contact, :join_table => :users_entities,
> :left_key => :user_id, :right_key => :entity_id
>
> Entity model:
>
> many_to_many :users,
> :class => User, :join_table => :users_entities,
> :left_key => :entity_id , :right_key => :user_id
>
> In my view, I like to be able to indicate that an entity is a favorite:
>
> class EntitiesController
>
> get '/' do
> @entities = Entity.sequel_append(:is_favorite, [a correlated, scalar
> query]).order(:name).all
> erb :'contacts/index'
> end
>
> end
>
>
> If I was going to approach this as a query, I would write:
>
> select e.*
> ,case
> when (
> select 1
> from users_entities
> where entity_id = e.id
> and user_id=[user_id]
> ) then 'Y'
> else 'N'
> end is_favorite
> from entities e
>
>
> This is a step in the right direction:
>
> DB[:entities].join(:users_entities, [[:entity_id,:id]]).where(user_id: 1)
>
> What's the recommend approach to including the sub-query? Maybe using
> empty?
>
A direct translation of your SQL to Sequel would be:
ds = DB[:users_entities].
where{{:entity_id=>e[:id]}}.
where(user_id: 1).
select(1)
Entity.from{entities.as(:e)}.
select_all(:e).
select_append(Sequel.case({ds=>'Y'}, 'N').as(:is_favorite))
Since you don't have a join table, you could probably drop the from and
select_all from the second query.
Thanks,
Jeremy
--
You received this message because you are subscribed to the Google Groups
"sequel-talk" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to [email protected].
To post to this group, send email to [email protected].
Visit this group at https://groups.google.com/group/sequel-talk.
For more options, visit https://groups.google.com/d/optout.
