public static Task <IReadOnlyCollection <string> > MissingUsers(this YtCollectDbCtx ctx, int?limit = null)
{
limit ??= ctx.YtCfg.MaxMissingUsers;
return(ctx.Db.Query <string>("missing users", @$ "
select distinct author_channel_id
from comment t
join video_latest v on v.video_id = t.video_id
where
platform = 'YouTube'
and not exists(select * from user where user_id = author_channel_id)
{limit.Dot(i => $" limit {
i
} ")}
"));
}
public static async Task <ChannelUpdatePlan[]> DiscoverChannelsViaRecs(this YtCollectDbCtx ctx)
{
var toAdd = await ctx.Db.Query <(string channel_id, string channel_title, string source)>("channels to classify",
@"with review_channels as (
select channel_id
, channel_title -- probably missing values. reviews without channels don't have titles
from channel_review r
where not exists(select * from channel_stage c where c.v:ChannelId::string=r.channel_id)
)
, rec_channels as (
select to_channel_id as channel_id, any_value(to_channel_title) as channel_title
from rec r
where to_channel_id is not null
and not exists(select * from channel_stage c where c.v:ChannelId::string=r.to_channel_id)
group by to_channel_id
)
, s as (
select channel_id, channel_title, 'review' as source
from review_channels sample (:remaining rows)
union all
select channel_id, channel_title, 'rec' as source
from rec_channels sample (:remaining rows)
)
select *
from s
limit :remaining", new { remaining = ctx.YtCfg.DiscoverChannels });
ctx.Log.Debug("Collect - found {Channels} new channels for discovery", toAdd.Count);
var toDiscover = toAdd
.Select(c => new ChannelUpdatePlan {
Channel = new() {
ChannelId = c.channel_id,
ChannelTitle = c.channel_title
},
ChannelUpdate = c.source == "review" ? ChannelUpdateType.Standard : ChannelUpdateType.Discover
})
public static Task <IReadOnlyCollection <string> > MissingUsers(this YtCollectDbCtx ctx) =>
ctx.Db.Query <string>("missing users", @$ "
select distinct author_channel_id
/// <summary>Existing reviewed channels with information on the last updates to extra parts.
/// <param name="channelSelect">By default will return channels that meet review criteria. To override, specify a select
/// query that returns rows with a column named channel_id</param>
/// </summary>
public static async Task <IReadOnlyCollection <ChannelUpdatePlan> > ChannelUpdateStats(this YtCollectDbCtx ctx,
IReadOnlyCollection <string> chans = null, string channelSelect = null)
{
channelSelect ??= @$ "
select channel_id from channel_latest
where platform = 'YouTube' and {(chans.None() ? " meets_review_criteria " : $" channel_id in ({ SqlList(chans) }) ")}";
var channels = await ctx.Db.Query <(string j, long?daysBack,
DateTime?lastVideoUpdate, DateTime?lastCaptionUpdate, DateTime?lastRecUpdate, DateTime?lastCommentUpdate)>(
"channels - previous",
$@"
with channels_raw as (
select distinct channel_id from ({channelSelect})
where channel_id is not null
)
, stage_latest as (
select v
from channel_stage -- query from stage because it can be deserialized without modification
where exists(select * from channels_raw r where r.channel_id=v:ChannelId)
qualify row_number() over (partition by v:ChannelId::string order by v:Updated::timestamp_ntz desc)=1
)
select coalesce(v, object_construct('ChannelId', r.channel_id)) channel_json
, b.daily_update_days_back
, (select max(v:Updated::timestamp_ntz) from video_stage where v:ChannelId=r.channel_id) last_video_update
, (select max(v:Updated::timestamp_ntz) from caption_stage where v:ChannelId=r.channel_id) last_caption_update
, (select max(v:Updated::timestamp_ntz) from rec_stage where v:FromChannelId=r.channel_id) last_rec_update
, (select max(v:Updated::timestamp_ntz) from comment_stage where v:ChannelId=r.channel_id) last_comment_update
from channels_raw r
left join stage_latest on v:ChannelId=r.channel_id
left join channel_collection_days_back b on b.channel_id=v:ChannelId
");
return(channels.Select(r => new ChannelUpdatePlan {
Channel = r.j.ToObject <Channel>(IJsonlStore.JCfg),
VideosFrom = r.daysBack != null ? DateTime.UtcNow - r.daysBack.Value.Days() : null,
LastVideoUpdate = r.lastVideoUpdate,
LastCaptionUpdate = r.lastCaptionUpdate,
LastRecUpdate = r.lastRecUpdate,
LastCommentUpdate = r.lastCommentUpdate
}).ToArray());
}
