That worked well. I didn't realize that I could compose queries in that
manner.
Thank you.
On Thursday, November 15, 2018 at 1:05:35 AM UTC-6, Jeremy Evans wrote:
>
> 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.
