Powersync reactitvity

audio_collections.tsx

export const DiscoverSection = memo(() => {
  const { data: discover, isLoading: isDiscoverLoading } = useQuery({
    queryKey: ['discover-home'],
    query: getDiscoverHome(),
  })

  // useSetQueryDataCollection(discover)

  return (
    <HorizontalListSection
      title={t('discover')}
      data={discover || []}
      isLoading={isDiscoverLoading}
      cardWidth={260}
      height={370}
      renderItem={(item: AudioCollection) => (
        <CollectionContextMenu collection={item}>
          <BigAudioCollectionCard
            {...item}
            paidAccessOnly={item.paid_access === 1}
          />
        </CollectionContextMenu>
      )}
    />
  )
})

createAudioTracksQuery.ts

export const createAudioTracksQuery = () => {
  const eb = expressionBuilder<Database, never>()
  return eb
    .selectFrom('audio_tracks as at')
    .select([
      'at.id',
      'at.bible_reference',
      'at.estimated_duration',
      'at.order_in_playlist',
      'at.mood',
      'at.author',
    ])
    .leftJoin('audio_tracks_translations as nt', (join) =>
      join
        .onRef('nt.audio_tracks_id', '=', 'at.id')
        .on('nt.languages_code', '=', getLanguage())
    )
    .leftJoin('audio_tracks_translations as en_nt', (join) =>
      join
        .onRef('en_nt.audio_tracks_id', '=', 'at.id')
        .on('en_nt.languages_code', '=', 'en')
    )
    .select([
      (eb) => eb.fn.coalesce('nt.name', 'en_nt.name').as('name'),
      (eb) => eb.fn.coalesce('nt.audio_url', 'at.audio_url').as('audio_url'),
      (eb) =>
        eb.fn
          .coalesce('nt.transcript_json_url', 'at.transcript_json_url')
          .as('transcript_json_url'),
    ])
}

createPlaylistQuery.ts

export const createPlaylistsQuery = (
  eb: ExpressionBuilder<Database, never>
) => {
  return eb
    .selectFrom('playlists as p')
    .select([
      'p.id',
      'p.order_in_collection',
      'p.bible_tracks_json',
      'p.estimated_duration',
      'p.audio_collection_id',
      'p.theme',
    ])
    .leftJoin('playlists_translations as nt', (join) =>
      join
        .onRef('nt.playlists_id', '=', 'p.id')
        .on('nt.languages_code', '=', getLanguage())
    )
    .leftJoin('playlists_translations as en_nt', (join) =>
      join
        .onRef('en_nt.playlists_id', '=', 'p.id')
        .on('en_nt.languages_code', '=', 'en')
    )
    .select([
      (eb) => eb.fn.coalesce('nt.name', 'en_nt.name').as('name'),
      (eb) =>
        eb.fn.coalesce('nt.description', 'en_nt.description').as('description'),
    ])
    .leftJoin('audio_collections as ac1', 'ac1.id', 'p.audio_collection_id')
    .leftJoin('directus_files as df', 'df.id', 'ac1.image')
    .leftJoin('audio_collections_translations as act', (join) =>
      join
        .onRef('act.audio_collections_id', '=', 'ac1.id')
        .on('act.languages_code', '=', getLanguage())
    )
    .leftJoin('audio_collections_translations as en_act', (join) =>
      join
        .onRef('en_act.audio_collections_id', '=', 'ac1.id')
        .on('en_act.languages_code', '=', 'en')
    )
    .select([
      'df.id as collection_image',
      'df.blurhash as collection_blurhash',
      'ac1.theme as collection_theme',
      'ac1.type as collection_type',
      (eb) => eb.fn.coalesce('act.name', 'en_act.name').as('collection_name'),
    ])
    .leftJoin('directus_files as dff', 'dff.id', 'p.image')
    .select(['dff.id as image', 'dff.blurhash as blurhash'])
    .leftJoin('user_favorites as ufp', (join) =>
      join.onRef('ufp.target_id', '=', 'p.id')
    )
    .select([
      (eb) =>
        eb
          .case()
          .when('ufp.target_id', 'is not', null)
          .then(true)
          .else(false)
          .end()
          .as('is_favorite'),
    ])
}

getAudioCollections.ts

export const getAudioCollections = ({
  playlistLimit = 'all',
  userFavorites = true,
  userOngoingPlans = true,
}: GetAudioCollectionsOptions = {}) =>
  system.db
    .selectFrom('audio_collections as ad')
    .select([
      'ad.id',
      'ad.type',
      'ad.theme',
      'ad.status',
      'ad.estimated_duration',
      'ad.paid_access',
      'ad.updated_at',
      'ad.lang',
      (eb) =>
        eb
          .selectFrom('playlists')
          .select((eb) => eb.fn.countAll().as('count'))
          .whereRef('audio_collection_id', '=', 'ad.id')
          .as('playlistCount'),
    ])

    .leftJoin('audio_collections_translations as nt', (join) =>
      join
        .onRef('nt.audio_collections_id', '=', 'ad.id')
        .on('nt.languages_code', '=', getLanguage())
    )
    .leftJoin('audio_collections_translations as en_nt', (join) =>
      join
        .onRef('en_nt.audio_collections_id', '=', 'ad.id')
        .on('en_nt.languages_code', '=', 'en')
    )
    .select([
      (eb) => eb.fn.coalesce('nt.name', 'en_nt.name').as('name'),
      (eb) =>
        eb.fn.coalesce('nt.description', 'en_nt.description').as('description'),
    ])
    .leftJoin('directus_files as df', 'df.id', 'ad.image')
    .select(['df.id as image', 'df.blurhash as blurhash'])
    .where((eb) => {
      const statusArray = [eb('ad.status', '=', 'published')]
      if (getDefaultStore().get(previewModeAtom)) {
        statusArray.push(eb('ad.status', '=', 'draft'))
      }

      const langArray = [
        eb('ad.lang', 'is', null),
        eb('ad.lang', '=', getLanguage()),
      ]

      if (getDefaultStore().get(allowEnglishContentAtom)) {
        langArray.push(eb('ad.lang', '=', 'en'))
      }

      return eb.and([eb.or(statusArray), eb.or(langArray)])
    })
    .select((eb) => [
      jsonArrayFrom(
        createPlaylistsQuery(eb as any)
          .select((eb) => [
            jsonArrayFrom(
              // @ts-expect-error
              createAudioTracksQuery().whereRef('at.playlist_id', '=', 'p.id')
            ).as('tracks'),
          ])
          // @ts-expect-error
          .whereRef('p.audio_collection_id', '=', 'ad.id')
          .$if(playlistLimit === 'first', (qb) =>
            qb.where('p.order_in_collection', '=', 1)
          )
      ).as('playlists'),
    ])
    .$if(userFavorites, (qb) =>
      qb
        .leftJoin('user_favorites as uf', (join) =>
          join.onRef('uf.target_id', '=', 'ad.id')
        )
        .select([
          (eb) =>
            eb
              .case()
              .when('uf.target_id', 'is not', null)
              .then(true)
              .else(false)
              .end()
              .as('is_favorite'),
        ])
    )
    .$if(userOngoingPlans, (qb) =>
      qb
        .leftJoin('user_ongoing_plans as uop', 'uop.collection_id', 'ad.id')
        .select([
          'uop.id as ongoing_plan_id',
          'uop.started_at',
          'uop.completed_at',
          'uop.playlists as ongoing_playlists',
          'uop.notification_id',
          'uop.notification_date',
          'uop.user_id',
        ])
        .select([
          (eb) =>
            eb
              .case()
              .when('uop.playlists', 'is not', null)
              .then(true)
              .else(false)
              .end()
              .as('is_ongoing_plan'),
        ])
    )

getDiscoverHome.ts

const baseHomeCollectionQuery = (
  tableName: keyof Pick<
    Database,
    | 'home_audio_collections'
    | 'home_audio_collections_1'
    | 'home_audio_collections_2'
    | 'home_audio_collections_3'
  >
) =>
  getAudioCollections({
    playlistLimit: 'first',
  })
    .innerJoin(`${tableName} as hac`, 'hac.audio_collections_id', 'ad.id')
    // @ts-expect-error
    .where('hac.home_id', '=', 'home')
    .orderBy('hac.order')

export const getDiscoverHome = () =>
  baseHomeCollectionQuery('home_audio_collections')

schema.ts

const audio_collections = new Table({
  type: column.text,
  name_translation_id: column.text,
  description_translation_id: column.text,
  image: column.text,
  theme: column.text,
  status: column.text,
  estimated_duration: column.integer,
  paid_access: column.integer,
  created_at: column.text,
  updated_at: column.text,
  lang: column.text,
})

const audio_collections_translations = new Table({
  audio_collections_id: column.text,
  languages_code: column.text,
  name: column.text,
  description: column.text,
})

const audio_tracks = new Table(
  {
    playlist_id: column.text,
    name_translation_id: column.text,
    bible_reference: column.text,
    audio_url: column.text,
    transcript_json_url: column.text,
    download_audio_url: column.text,
    image: column.text,
    order_in_playlist: column.integer,
    mood: column.text,
    author: column.text,
    estimated_duration: column.integer,
  },
  { indexes: { playlist: ['playlist_id'], order: ['order_in_playlist'] } }
)

const audio_tracks_translations = new Table({
  audio_tracks_id: column.text,
  languages_code: column.text,
  name: column.text,
  audio_url: column.text,
  transcript_json_url: column.text,
  download_audio_url: column.text,
})

const audio_collections_tags = new Table(
  {
    audio_collections_id: column.text,
    tags_id: column.text,
  },
  { indexes: { audio_collection: ['audio_collections_id'], tag: ['tags_id'] } }
)

const playlists = new Table(
  {
    audio_collection_id: column.text,
    name_translation_id: column.text,
    description_translation_id: column.text,
    image: column.text,
    order_in_collection: column.integer,
    bible_tracks_json: column.text,
    theme: column.text,
    estimated_duration: column.integer,
    created_at: column.text,
    updated_at: column.text,
  },
  {
    indexes: {
      audio_collection: ['audio_collection_id'],
      order: ['order_in_collection'],
    },
  }
)

const user_favorites = new Table(
  {
    type: column.text,
    target_id: column.text,
    created_at: column.text,
    user_id: column.text,
  },
  {
    indexes: {
      collection: ['collection_id'],
      playlist: ['playlist_id'],
      user: ['user_id'],
    },
  }
)