how to write this recursive query in Sequel
Hi,
I'm sorry my previous email was mistakenly wrong. This is the correct one.
I mixed up the queries.
with recursive pattern(pattern) as (
select string_to_array('folder_2/SUB iso', '/') -- input
),
full_paths as (
select id, base_folder_id, subject, 1 as idx
from folders
cross join pattern
where user_id = 24 and subject ILIKE pattern[1]
union all
select x.id, x.base_folder_id, x.subject, idx+ 1
from folders as x
cross join pattern
inner join full_paths as y
on x.base_folder_id = y.id
and x.subject ILIKE pattern[idx+ 1]
)
select id, subject
from full_paths
cross join pattern
where idx = cardinality(pattern)
I couldn't transform this to Sequel properly. Is there any way to do this
or should do a function?
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: how to write this recursive query in Sequel
Hi Jeremy,
Thank you for the help. I really appreciated it.
Is it possible to return this query as a model? How can I reflect this to a
model? So it doesn't return just as hash.
P.S.: Yes, only a single item will return in my query.
Thanks,
Gencer.
On Thursday, October 11, 2018 at 9:31:06 PM UTC+3, genc wrote:
>
> Hi,
>
> I'm sorry my previous email was mistakenly wrong. This is the correct one.
> I mixed up the queries.
>
>
> with recursive pattern(pattern) as (
> select string_to_array('folder_2/SUB iso', '/') -- input
> ),
> full_paths as (
> select id, base_folder_id, subject, 1 as idx
> from folders
> cross join pattern
> where user_id = 24 and subject ILIKE pattern[1]
> union all
> select x.id, x.base_folder_id, x.subject, idx+ 1
> from folders as x
> cross join pattern
> inner join full_paths as y
> on x.base_folder_id = y.id
> and x.subject ILIKE pattern[idx+ 1]
> )
> select id, subject
> from full_paths
> cross join pattern
> where idx = cardinality(pattern)
>
>
> I couldn't transform this to Sequel properly. Is there any way to do this
> or should do a function?
>
> 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: how to write this recursive query in Sequel
Hi Jeremy,
Thank you so much!!!
However, Unfortunately given example produces an error and says:
> ERROR: column pattern.base_folder_id does not exist
LINE 1: ...n" INNER JOIN "full_paths" AS "y" ON (("y"."id" = "pattern"
I think In your query, :pattern matched with :y instead of :x with :y when
inner join created. I tried to manually fix it but unable to do so.
Thanks,
Gencer.
On Thursday, October 11, 2018 at 10:34:12 PM UTC+3, Jeremy Evans wrote:
>
> On Thursday, October 11, 2018 at 11:31:06 AM UTC-7, genc wrote:
>>
>> Hi,
>>
>> I'm sorry my previous email was mistakenly wrong. This is the correct
>> one. I mixed up the queries.
>>
>>
>> with recursive pattern(pattern) as (
>> select string_to_array('folder_2/SUB iso', '/') -- input
>> ),
>> full_paths as (
>> select id, base_folder_id, subject, 1 as idx
>> from folders
>> cross join pattern
>> where user_id = 24 and subject ILIKE pattern[1]
>> union all
>> select x.id, x.base_folder_id, x.subject, idx+ 1
>> from folders as x
>> cross join pattern
>> inner join full_paths as y
>> on x.base_folder_id = y.id
>> and x.subject ILIKE pattern[idx+ 1]
>> )
>> select id, subject
>> from full_paths
>> cross join pattern
>> where idx = cardinality(pattern)
>>
>>
>> I couldn't transform this to Sequel properly. Is there any way to do this
>> or should do a function?
>>
>
> You can do this with Sequel:
>
> DB[:full_paths].
> select(:id, :subject).
> cross_join(:pattern).
> where{{:idx=>cardinality(:pattern)}}.
> with(:pattern, DB.select{string_to_array("folder_2/SUB iso", '/')},
> :args=>[:pattern]).
> with_recursive(:full_paths,
> DB[:folders].
> select(:id, :base_folder_id, :subject, Sequel[1].as(:idx)).
> cross_join(:pattern).
> where(:user_id=>24).
> where{subject.ilike(pattern.sql_subscript(1))},
> DB[Sequel[:folders].as(:x)].
> select(:id, :base_folder_id, :subject, Sequel[:idx] + 1).
> cross_join(:pattern).
> join(Sequel[:full_paths].as(:y),
> :id=>:base_folder_id){Sequel.expr{x[:subject].ilike(pattern.sql_subscript(idx+1))}}
> )
>
> WITH RECURSIVE "pattern"("pattern") AS (
> SELECT string_to_array('folder_2/SUB iso', '/')
> ),
> "full_paths" AS (
> SELECT "id", "base_folder_id", "subject", 1 AS "idx"
> FROM "folders"
> CROSS JOIN "pattern"
> WHERE (("user_id" = 24) AND ("subject" ILIKE "pattern"[1] ESCAPE '\'))
> UNION ALL (
> SELECT "id", "base_folder_id", "subject", ("idx" + 1)
> FROM "folders" AS "x"
> CROSS JOIN "pattern"
> INNER JOIN "full_paths" AS "y"
> ON (("y"."id" = "pattern"."base_folder_id")
> AND ("x"."subject" ILIKE "pattern"[("idx" + 1)] ESCAPE '\')))
> )
> SELECT "id", "subject"
> FROM "full_paths"
> CROSS JOIN "pattern"
> WHERE ("idx" = cardinality("pattern"))
>
> 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 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: how to write this recursive query in Sequel
Thank you so much, Jeremy.
Have a great weekend!,
Gencer.
On Thursday, October 11, 2018 at 9:31:06 PM UTC+3, genc wrote:
>
> Hi,
>
> I'm sorry my previous email was mistakenly wrong. This is the correct one.
> I mixed up the queries.
>
>
> with recursive pattern(pattern) as (
> select string_to_array('folder_2/SUB iso', '/') -- input
> ),
> full_paths as (
> select id, base_folder_id, subject, 1 as idx
> from folders
> cross join pattern
> where user_id = 24 and subject ILIKE pattern[1]
> union all
> select x.id, x.base_folder_id, x.subject, idx+ 1
> from folders as x
> cross join pattern
> inner join full_paths as y
> on x.base_folder_id = y.id
> and x.subject ILIKE pattern[idx+ 1]
> )
> select id, subject
> from full_paths
> cross join pattern
> where idx = cardinality(pattern)
>
>
> I couldn't transform this to Sequel properly. Is there any way to do this
> or should do a function?
>
> 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.
How to append data on existing field
Hi Jeremy,
I am trying to achieve this:
update files set name = '--DELETED--' || files.name
basically i would like to prepend deleted text to fields. How can I do that
with Sequel? I couldn't find any documentation about transforming existing
data on the fly with sequel.
I tried something like:
...update{ ... }. However, it seems Update method does not support brackets.
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.
Support for "NOT EXISTS" available?
Hi Jeremy, I dig into querying.md and can't find any example about "NOT EXISTS". How can we write this query in Sequel: select * from books where not exists (select * from writers where .) 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: syntax for ORed where clauses?
Thank you, Jeremy! It did worked. One last question. About migrations. I have a few foreign keys already declared and they are set to "on delete SET NULL". I would like to change them to "on delete CASCADE". I know there was a way of doing this but alter_table seems not mentioned about this. How can I change cascade styule for an existing foreign key using migrations? Thanks, Gencer. On Wednesday, June 25, 2014 at 1:54:21 PM UTC+3, Simon Dahlbacka wrote: > > Hi, > > I've tried to read the docs but haven't been able to figure out how to get > the two following where clauses ORed together > > .where(:OACUOR => request[:customersOrderNumber]) > .where(db[:OOLINE].where( >:OBCONO => request[:company], >:OBORNO => :OAORNO, >:OBCUOR => request[:customersOrderNumber]).exists)) > > i.e. > > the resulting SQL should be > > select * from foo WHERE (:OACUOR='foo') OR (EXISTS(SELECT * FROM OOLINE > WHERE OBCONO=100 AND OBORNO=OAORNO AND OBCUOR='foo')) > > > How to achieve that? (I'm using the jdbc/as400 adapter if that matters) > > > And thanks for sequel btw! > > regards, > Simon > -- 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: Support for "NOT EXISTS" available?
Thank you, Jeremy! It did worked. One last question. About migrations. I have a few foreign keys already declared and they are set to "on delete SET NULL". I would like to change them to "on delete CASCADE". I know there was a way of doing this but alter_table seems not mentioned about this. How can I change cascade styule for an existing foreign key using migrations? Thanks, Gencer. On Tuesday, October 23, 2018 at 6:28:24 PM UTC+3, Jeremy Evans wrote: > > On Tuesday, October 23, 2018 at 7:46:47 AM UTC-7, genc wrote: >> >> Hi Jeremy, >> >> I dig into querying.md and can't find any example about "NOT EXISTS". >> >> How can we write this query in Sequel: >> >> select * from books where not exists (select * from writers where .) >> >> > I doubt NOT EXISTS would be documented explicitly. However, you just need > to combine the NOT with the EXISTS: > > DB[:books].exclude(DB[:writers].where(:foo).exists) > > 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 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: Support for "NOT EXISTS" available?
Thank you so much, Jeremy. On Tuesday, October 23, 2018 at 5:46:47 PM UTC+3, genc wrote: > > Hi Jeremy, > > I dig into querying.md and can't find any example about "NOT EXISTS". > > How can we write this query in Sequel: > > select * from books where not exists (select * from writers where .) > > > 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.
adding more conditions on inner join when using recursive
Hi Jeremy,
I want to add more conditions to inner join after ON but it appends and
treat as array instead of merging conditions.
Let me show you the full query builder:
DB[:full_paths].
select(:id, :subject).
cross_join(:pattern).
where{{:idx=>cardinality(:pattern)}}.
with(:pattern, DB.select{string_to_array("folder_2/SUB iso", '/')}, :args
=>[:pattern]).
with_recursive(:full_paths,
DB[:folders].
select(:id, :base_folder_id, :subject, Sequel[1].as(:idx)).
cross_join(:pattern).
where(:user_id=>24).
where{subject.ilike(pattern.sql_subscript(1))},
DB[Sequel[:folders].as(:x)].
select(:id, :base_folder_id, :subject, Sequel[:idx] + 1).
cross_join(:pattern).
join(Sequel[:full_paths].as(:y), :id=>:base_folder_id){Sequel.expr{x[:
subject].ilike(pattern.sql_subscript(idx+1))}}
)
What I want to add here: (last line)
join(Sequel[:full_paths].as(:y), :id=>:base_folder_id){Sequel.expr{x[:
subject].ilike(pattern.sql_subscript(idx+1))}}
more conditions which is:
x.user_id = 24
Currently this line produces:
INNER JOIN full_paths AS y ON ((y.id = x.base_folder_id) AND (x.subject
ILIKE pattern[(idx + 1)] ESCAPE '\\'
What I want is:
INNER JOIN full_paths AS y ON ((y.id = x.base_folder_id) AND (x.user_id = 4)
AND (x.subject ILIKE pattern[(idx + 1)] ESCAPE '\\'
How can i make multiple conditions for this join (not where but via ON)
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: adding more conditions on inner join when using recursive
Ah, I merged them with &
Sequel.expr { x[:user_id] =~ 4 } & ...
On Thursday, November 15, 2018 at 11:43:03 AM UTC+3, genc wrote:
>
> Hi Jeremy,
>
> I want to add more conditions to inner join after ON but it appends and
> treat as array instead of merging conditions.
>
> Let me show you the full query builder:
>
> DB[:full_paths].
> select(:id, :subject).
> cross_join(:pattern).
> where{{:idx=>cardinality(:pattern)}}.
> with(:pattern, DB.select{string_to_array("folder_2/SUB iso", '/')}, :
> args=>[:pattern]).
> with_recursive(:full_paths,
> DB[:folders].
> select(:id, :base_folder_id, :subject, Sequel[1].as(:idx)).
> cross_join(:pattern).
> where(:user_id=>24).
> where{subject.ilike(pattern.sql_subscript(1))},
> DB[Sequel[:folders].as(:x)].
> select(:id, :base_folder_id, :subject, Sequel[:idx] + 1).
> cross_join(:pattern).
> join(Sequel[:full_paths].as(:y), :id=>:base_folder_id){Sequel.expr{x
> [:subject].ilike(pattern.sql_subscript(idx+1))}}
> )
>
>
> What I want to add here: (last line)
>
> join(Sequel[:full_paths].as(:y), :id=>:base_folder_id){Sequel.expr{x
> [:subject].ilike(pattern.sql_subscript(idx+1))}}
>
>
> more conditions which is:
>
> x.user_id = 24
>
> Currently this line produces:
>
> INNER JOIN full_paths AS y ON ((y.id = x.base_folder_id) AND (x.subject
> ILIKE pattern[(idx + 1)] ESCAPE '\\'
>
>
> What I want is:
>
> INNER JOIN full_paths AS y ON ((y.id = x.base_folder_id) AND (x.user_id =
> 4) AND (x.subject ILIKE pattern[(idx + 1)] ESCAPE '\\'
>
>
> How can i make multiple conditions for this join (not where but via ON)
>
> 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
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.
Second left join for sum() sees previous left join instead of main table
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: querying joined tables and sum all
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.
Re: Second left join for sum() sees previous left join instead of main table
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
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
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.
How to make sure jsonb fields are symbolic hashes when fetched?
Hi Jeremy,
When I do this:
class User < Sequel::Model
...
end
user = User.new
user.properties = { name: 'Genc', age: 42 } # or ':name =>..., '
user.save
#try to fetch assigned data
user.properties[:name] # => Genc
user.properties[:age] # => 42
However, If I fetch that data later or again like this:
# fetch user 1
user = User[1]
user.properties[:name] # => nil
user.properties[:age] # => nil
# Try string keys
user.properties['name'] # => Genc
user.properties['age'] # => 42
As you can see I did not use :symbol because the keys are strings not
symbols. How can I make Sequel *globally *fetch all keys as symbolic?
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.
Creating master method for models
Hi Jeremy, I have models like this: class Mailbox::Email < Mailbox::Model(:emails) end class Mailbox::Post < Mailbox::Model(:emails) end and of course a base class: module Mailbox def self.Model(source) c = Sequel::Model(second_db) c.set_dataset(source) end # defining admin? here not work as its inhertied from Model. end Now, on each model I put a method class Mailbox::Email < Mailbox::Model(:emails) def admin? is_admin || false end end class Mailbox::Post < Mailbox::Model(:posts) def admin? is_admin || false end end Now, what I am trying to do is remove duplicate code and make each model get admin method from parent and inherit. So whatever I fetch data i always have *admin? *method available for each model How can I inject this method to base class? Because putting it under Mailbox does not work. What I tried so far: define_method :admin? do 'gencer' end # inside of Model. def admin? 'gencer' end # Inside of Mailbox Without luck. I want to each method process info for its own model but method should be global. 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: How to make sure jsonb fields are symbolic hashes when fetched?
Gotcha.
Thanks Jeremy.
On Tuesday, December 25, 2018 at 6:37:58 PM UTC+3, genc wrote:
>
> Hi Jeremy,
>
> When I do this:
>
> class User < Sequel::Model
> ...
> end
>
> user = User.new
> user.properties = { name: 'Genc', age: 42 } # or ':name =>..., '
> user.save
>
> #try to fetch assigned data
> user.properties[:name] # => Genc
> user.properties[:age] # => 42
>
>
> However, If I fetch that data later or again like this:
>
> # fetch user 1
> user = User[1]
>
> user.properties[:name] # => nil
> user.properties[:age] # => nil
> # Try string keys
> user.properties['name'] # => Genc
> user.properties['age'] # => 42
>
>
> As you can see I did not use :symbol because the keys are strings not
> symbols. How can I make Sequel *globally *fetch all keys as symbolic?
>
> 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: Creating master method for models
Thanks Jeremy. But this time it doesn't see my dataset assignment. It used primary connection in rails. I think I put these on wrong place: c = Sequel::Model(second_db) c.set_dataset(source) Gencer. On Tuesday, December 25, 2018 at 9:32:23 PM UTC+3, Jeremy Evans wrote: > > On Tuesday, December 25, 2018 at 8:50:32 AM UTC-8, genc wrote: >> >> Hi Jeremy, >> >> I have models like this: >> >> >> class Mailbox::Email < Mailbox::Model(:emails) >> end >> class Mailbox::Post < Mailbox::Model(:emails) >> end >> >> >> and of course a base class: >> >> module Mailbox >> def self.Model(source) >> c = Sequel::Model(second_db) >> c.set_dataset(source) >> end >> >> # defining admin? here not work as its inhertied from Model. >> end >> > > You want to use Class.new(Sequel::Model) to create the abstract base > class, and def_Model to define the appropriate model method. Then you can > use class_eval do define the methods shared by subclasses: > > module Mailbox > Model = Class.new(Sequel::Model) > Model.def_Model(self) > Model.class_eval do > def admin? > is_admin || false > end > end > end > > 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 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: Creating master method for models
no not dataset, connection is wrong. I tried to inbject in method but no luck. Jeremy, How can I inject connection (second_db in this example) using your way? On Tuesday, December 25, 2018 at 10:31:42 PM UTC+3, genc wrote: > > Thanks Jeremy. > > But this time it doesn't see my dataset assignment. It used primary > connection in rails. I think I put these on wrong place: > > c = Sequel::Model(second_db) > c.set_dataset(source) > > Gencer. > > On Tuesday, December 25, 2018 at 9:32:23 PM UTC+3, Jeremy Evans wrote: >> >> On Tuesday, December 25, 2018 at 8:50:32 AM UTC-8, genc wrote: >>> >>> Hi Jeremy, >>> >>> I have models like this: >>> >>> >>> class Mailbox::Email < Mailbox::Model(:emails) >>> end >>> class Mailbox::Post < Mailbox::Model(:emails) >>> end >>> >>> >>> and of course a base class: >>> >>> module Mailbox >>> def self.Model(source) >>> c = Sequel::Model(second_db) >>> c.set_dataset(source) >>> end >>> >>> # defining admin? here not work as its inhertied from Model. >>> end >>> >> >> You want to use Class.new(Sequel::Model) to create the abstract base >> class, and def_Model to define the appropriate model method. Then you can >> use class_eval do define the methods shared by subclasses: >> >> module Mailbox >> Model = Class.new(Sequel::Model) >> Model.def_Model(self) >> Model.class_eval do >> def admin? >> is_admin || false >> end >> end >> end >> >> 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 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: Creating master method for models
Thanks Jeremy. It works like a charm. I just able to inject connection. I was missing one line :) On Tuesday, December 25, 2018 at 7:50:32 PM UTC+3, genc wrote: > > Hi Jeremy, > > I have models like this: > > > class Mailbox::Email < Mailbox::Model(:emails) > end > class Mailbox::Post < Mailbox::Model(:emails) > end > > > and of course a base class: > > module Mailbox > def self.Model(source) > c = Sequel::Model(second_db) > c.set_dataset(source) > end > > # defining admin? here not work as its inhertied from Model. > end > > > Now, on each model I put a method > > > class Mailbox::Email < Mailbox::Model(:emails) > def admin? > is_admin || false > end > end > > > class Mailbox::Post < Mailbox::Model(:posts) > def admin? > is_admin || false > end > end > > > Now, what I am trying to do is remove duplicate code and make each model > get admin method from parent and inherit. So whatever I fetch data i always > have *admin? *method available for each model > > How can I inject this method to base class? Because putting it under > Mailbox does not work. > > What I tried so far: > > define_method :admin? do > 'gencer' > end # inside of Model. > > def admin? > 'gencer' > end # Inside of Mailbox > > > Without luck. > > I want to each method process info for its own model but method should be > global. > > 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.
How to use Where IN "string_to_array" with Sequel?
Hi Jeremy,
On PostgreSQL there is a string_to_array function. This is currently What I
am doing:
UPDATE files SET date="2007-10-10 10:50:22" WHERE id in(1,2,3,4,5..)
However, IDs can be very large and can be oversize for a query. Due to this
I woulşd like to send them as text and split on psql side like this:
UPDATE files SET date="2007-10-10 10:50:22" WHERE id IN (SELECT unnest (
string_to_array('16767,16766' , ',')::bigint[]))
How can I achieve this with Sequel?
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.
how to add asterisk on count when using partition over
I have this sql line:
count(*) OVER (PARTITION BY sender->>'email') AS "count"
And I translated to this:
.select_append { count.function.over(partition: sender.get_text('email')).as
(:count) }
But this gives
count()
instead of
count(*)
This causes an error. How can i add asterisk to the count?
--
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.
To view this discussion on the web visit
https://groups.google.com/d/msgid/sequel-talk/37fc4f9d-570c-40a4-805d-a15de05da26b%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.
Re: how to add asterisk on count when using partition over
Ah figured out.
.select_append { Sequel.function(:count).*.over(partition: quiz_snapshot.
get_text('email')).as(:count) }
On Friday, May 24, 2019 at 2:03:54 AM UTC+3, genc wrote:
>
> I have this sql line:
>
> count(*) OVER (PARTITION BY sender->>'email') AS "count"
>
>
> And I translated to this:
>
> .select_append { count.function.over(partition: sender.get_text('email')).
> as(:count) }
>
> But this gives
> count()
> instead of
> count(*)
>
> This causes an error. How can i add asterisk to the count?
>
--
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.
To view this discussion on the web visit
https://groups.google.com/d/msgid/sequel-talk/04175e11-3a30-4f66-b557-0f26a1e83c53%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.
jsonb, LATERAL and DISTINCT query with sequel
Hi, Is it possible to write this query with Sequel? SELECT DISTINCT ON (x.e->>'email') x.e->>'email' as email, x.e->>'name' as name FROM messages sr CROSS JOIN LATERAL jsonb_array_elements(sr.receiver) x (e) ORDER BY x.e->>'email', id desc Or I use *with_sql?* Thanks! -- 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. To view this discussion on the web visit https://groups.google.com/d/msgid/sequel-talk/ef167f41-2fe9-4b97-b5c4-8e16058a1615%40googlegroups.com. For more options, visit https://groups.google.com/d/optout.
