Re: querying joined tables and sum all

2019-01-14 Thread genc 
I've achieved this by:

.where{ ( subscriptions[:expires_on] =~ nil ) | (subscriptions[:expires_on] 
<= Time.now.utc) }.sql



On Monday, January 14, 2019 at 10:03:38 PM UTC+3, genc wrote:
>
> Hi Jeremy,
>
> Sorry for previous question thread. I deleted it to not confusing.
>
> This is what I am trying to do:
>
> select sum(sp.storage_size) + sum(bp.storage_size) as storage_size
>   from subscriptions s 
>   left join subscription_packages sp on s.subscription_package_id = sp.id
>   left join bounty_packages bp on s.bounty_package_id = bp.id
> where s.user_id = 4 and
>(subscriptions.expires_on IS NULL OR subscriptions.expires_on <= 
> '2018-04-01 
> 10:10:10');
>
>
> This is what I tried so far:
>
> Subscription.select { Sequel.cast(sum(Sequel[:bounty_packages][:
> storage_size]) + sum(Sequel[:subscription_packages][:storage_size]), :
> bigint).as(:total_size) }
>   .left_join(:subscription_packages, id: :
> subscription_package_id)
>   .left_join(:bounty_packages, id: Sequel[:
> subscriptions][:bounty_package_id])
>   .where(user: user)
>   .where{ Sequel.('subscriptions.expires_on': nil) | 
> subscriptions__expires_on 
> <= Time.now.utc }.first
>
>
> Am I doing in correct way? Especially on where{} and sum() clauses. Is 
> there any better way of doing this?
>
> Thanks,
> Gencer
>

-- 
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 sequel-talk+unsubscr...@googlegroups.com.
To post to this group, send email to sequel-talk@googlegroups.com.
Visit this group at https://groups.google.com/group/sequel-talk.
For more options, visit https://groups.google.com/d/optout.

querying joined tables and sum all

2019-01-14 Thread genc 
Hi Jeremy,

Sorry for previous question thread. I deleted it to not confusing.

This is what I am trying to do:

select sum(sp.storage_size) + sum(bp.storage_size) as storage_size
  from subscriptions s 
  left join subscription_packages sp on s.subscription_package_id = sp.id
  left join bounty_packages bp on s.bounty_package_id = bp.id
where s.user_id = 4 and
   (subscriptions.expires_on IS NULL OR subscriptions.expires_on <= '2018-04-01 
10:10:10');


This is what I tried so far:

Subscription.select { Sequel.cast(sum(Sequel[:bounty_packages][:storage_size
]) + sum(Sequel[:subscription_packages][:storage_size]), :bigint).as(:
total_size) }
  .left_join(:subscription_packages, id: :
subscription_package_id)
  .left_join(:bounty_packages, id: Sequel[:subscriptions
][:bounty_package_id])
  .where(user: user)
  .where{ Sequel.('subscriptions.expires_on': nil) | 
subscriptions__expires_on 
<= Time.now.utc }.first


Am I doing in correct way? Especially on where{} and sum() clauses. Is 
there any better way of doing this?

Thanks,
Gencer

-- 
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 sequel-talk+unsubscr...@googlegroups.com.
To post to this group, send email to sequel-talk@googlegroups.com.
Visit this group at https://groups.google.com/group/sequel-talk.
For more options, visit https://groups.google.com/d/optout.

Re: Second left join for sum() sees previous left join instead of main table

2019-01-14 Thread genc 
I also solved sum field as this:

Subscription.select{ (sum( Sequel[:bounty_packages][:storage_size]) + sum(
Sequel[:subscription_packages][:storage_size])).as(:size) }

Once again, Let me know if there is a better way.

On Monday, January 14, 2019 at 8:59:09 PM UTC+3, genc wrote:
>
> Hi Jeremy,
>
> This is my SQL query:
>
> select sum(sp.storage_size) + sum(bp.storage_size) as storage_size
>   from subscriptions s 
>   left join subscription_packages sp on s.subscription_package_id = sp.id
>   left join bounty_packages bp on s.bounty_package_id = bp.id
> where s.user_id = 4;
>
>
> However, second left join is always broken:
>
> Subscription.select{ sum(Sequel[:'bounty_packages.storage_size']) + sum(
> Sequel[:'subscription_packages.storage_size']) }
> .left_join(:subscription_packages, id: :subscription_package_id)
> .left_join(:bounty_packages, id: :bounty_package_id) # this join 
> trying to join previous join (subscription_packages) not the main 
> 'subscriptions' table
> .where(user: user)
>
> Is there a wiser way to accomplish my query with Sequel?
>
> Thanks,
> Genc.
>

-- 
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 sequel-talk+unsubscr...@googlegroups.com.
To post to this group, send email to sequel-talk@googlegroups.com.
Visit this group at https://groups.google.com/group/sequel-talk.
For more options, visit https://groups.google.com/d/optout.

Re: Second left join for sum() sees previous left join instead of main table

2019-01-14 Thread genc 
One thing thsat i couldn't solve is that how to fetch total sum column as 
:storage_size.

select sum() + sum() as FIELD_NAME ...

In my scenario, there are two sums and i need to name them.



On Monday, January 14, 2019 at 9:36:11 PM UTC+3, genc wrote:
>
> As per documentation, I achieved this by:
>
> Subscription.select{ sum( Sequel[:bounty_packages][:storage_size]) + sum(
> Sequel[:subscription_packages][:storage_size]) }
> .left_join(:subscription_packages, id: :subscription_package_id)
> .left_join(:bounty_packages, id: Sequel[:subscriptions][:
> bounty_package_id])
> .where(user: user)
>
> Let me know if there is a better way.
>
> Thanks,
> Genc.
>
> On Monday, January 14, 2019 at 8:59:09 PM UTC+3, genc wrote:
>>
>> Hi Jeremy,
>>
>> This is my SQL query:
>>
>> select sum(sp.storage_size) + sum(bp.storage_size) as storage_size
>>   from subscriptions s 
>>   left join subscription_packages sp on s.subscription_package_id = sp.id
>>   left join bounty_packages bp on s.bounty_package_id = bp.id
>> where s.user_id = 4;
>>
>>
>> However, second left join is always broken:
>>
>> Subscription.select{ sum(Sequel[:'bounty_packages.storage_size']) + sum(
>> Sequel[:'subscription_packages.storage_size']) }
>> .left_join(:subscription_packages, id: :subscription_package_id)
>> .left_join(:bounty_packages, id: :bounty_package_id) # this join 
>> trying to join previous join (subscription_packages) not the main 
>> 'subscriptions' table
>> .where(user: user)
>>
>> Is there a wiser way to accomplish my query with Sequel?
>>
>> Thanks,
>> Genc.
>>
>

-- 
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 sequel-talk+unsubscr...@googlegroups.com.
To post to this group, send email to sequel-talk@googlegroups.com.
Visit this group at https://groups.google.com/group/sequel-talk.
For more options, visit https://groups.google.com/d/optout.

Re: Second left join for sum() sees previous left join instead of main table

2019-01-14 Thread genc 
As per documentation, I achieved this by:

Subscription.select{ sum( Sequel[:bounty_packages][:storage_size]) + sum(
Sequel[:subscription_packages][:storage_size]) }
.left_join(:subscription_packages, id: :subscription_package_id)
.left_join(:bounty_packages, id: Sequel[:subscriptions][:
bounty_package_id])
.where(user: user)

Let me know if there is a better way.

Thanks,
Genc.

On Monday, January 14, 2019 at 8:59:09 PM UTC+3, genc wrote:
>
> Hi Jeremy,
>
> This is my SQL query:
>
> select sum(sp.storage_size) + sum(bp.storage_size) as storage_size
>   from subscriptions s 
>   left join subscription_packages sp on s.subscription_package_id = sp.id
>   left join bounty_packages bp on s.bounty_package_id = bp.id
> where s.user_id = 4;
>
>
> However, second left join is always broken:
>
> Subscription.select{ sum(Sequel[:'bounty_packages.storage_size']) + sum(
> Sequel[:'subscription_packages.storage_size']) }
> .left_join(:subscription_packages, id: :subscription_package_id)
> .left_join(:bounty_packages, id: :bounty_package_id) # this join 
> trying to join previous join (subscription_packages) not the main 
> 'subscriptions' table
> .where(user: user)
>
> Is there a wiser way to accomplish my query with Sequel?
>
> Thanks,
> Genc.
>

-- 
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 sequel-talk+unsubscr...@googlegroups.com.
To post to this group, send email to sequel-talk@googlegroups.com.
Visit this group at https://groups.google.com/group/sequel-talk.
For more options, visit https://groups.google.com/d/optout.

Second left join for sum() sees previous left join instead of main table

2019-01-14 Thread genc 
Hi Jeremy,

This is my SQL query:

select sum(sp.storage_size) + sum(bp.storage_size) as storage_size
  from subscriptions s 
  left join subscription_packages sp on s.subscription_package_id = sp.id
  left join bounty_packages bp on s.bounty_package_id = bp.id
where s.user_id = 4;


However, second left join is always broken:

Subscription.select{ sum(Sequel[:'bounty_packages.storage_size']) + sum(
Sequel[:'subscription_packages.storage_size']) }
.left_join(:subscription_packages, id: :subscription_package_id)
.left_join(:bounty_packages, id: :bounty_package_id) # this join 
trying to join previous join (subscription_packages) not the main 
'subscriptions' table
.where(user: user)

Is there a wiser way to accomplish my query with Sequel?

Thanks,
Genc.

-- 
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 sequel-talk+unsubscr...@googlegroups.com.
To post to this group, send email to sequel-talk@googlegroups.com.
Visit this group at https://groups.google.com/group/sequel-talk.
For more options, visit https://groups.google.com/d/optout.