FraudCaseFilesWithCountsView

Columns

Column

Type

Size

Nulls

Auto

Default

Children

Parents

Comments

FraudCaseFileId

uuid

2147483647

√

null

CompanyId

uuid

2147483647

√

null

CompanyDeal.companyId

Implied Constraint

R

createdAt

timestamptz

35,6

√

null

updatedAt

timestamptz

35,6

√

null

status

enum_FraudCaseFiles_status

2147483647

√

null

statusReason

enum_FraudCaseFiles_statusReason

2147483647

√

null

reportedSource

enum_FraudCaseFiles_reportedSource

2147483647

√

null

totalReportCount

int8

19

√

null

pendingReportCount

int8

19

√

null

pendingInterceptedMessageCounts

int8

19

√

null

Relationships

Close relationships within degrees of separation

View Definition

WITH "ReportedJobCounts" AS (
         SELECT fcf_1.id AS "FraudCaseFileId",
            COALESCE(count(DISTINCT rj.id), (0)::bigint) AS rj_total_count,
            COALESCE(sum(
                CASE
                    WHEN (rj."resolutionStatus" = 'PENDING'::"enum_ReportResolutionStatus") THEN 1
                    ELSE 0
                END), (0)::bigint) AS rj_pending_count
           FROM (("FraudCaseFiles" fcf_1
             LEFT JOIN "Jobs" j ON ((j."CompanyId" = fcf_1."CompanyId")))
             LEFT JOIN "ReportedJobs" rj ON ((rj."JobId" = j.id)))
          GROUP BY fcf_1.id
        ), "SystemPredictionTotalJobs" AS (
         SELECT fcf_1.id AS "FraudCaseFileId",
            count(DISTINCT j.id) AS spl_total_job_count
           FROM (("FraudCaseFiles" fcf_1
             JOIN "Jobs" j ON ((j."CompanyId" = fcf_1."CompanyId")))
             JOIN "SystemPredictionLogs" spl ON ((spl."JobId" = j.id)))
          GROUP BY fcf_1.id
        ), "SystemPredictionPendingJobs" AS (
         SELECT fcf_1.id AS "FraudCaseFileId",
            count(DISTINCT j.id) AS spl_pending_job_count
           FROM ((("FraudCaseFiles" fcf_1
             JOIN "Jobs" j ON ((j."CompanyId" = fcf_1."CompanyId")))
             JOIN "SystemPredictionLogs" spl ON ((spl."JobId" = j.id)))
             LEFT JOIN "SystemPredictionResolutions" spr ON ((spr."JobId" = j.id)))
          WHERE ((spr.id IS NULL) OR (spr."resolutionStatus" = 'PENDING'::"enum_ReportResolutionStatus"))
          GROUP BY fcf_1.id
        ), "InterceptedMessageCounts" AS (
         SELECT fcf_1.id AS "FraudCaseFileId",
            COALESCE(count(DISTINCT ifm.id), (0)::bigint) AS ifm_total_count,
            COALESCE(sum(
                CASE
                    WHEN (ifm."resolutionStatus" = 'PENDING'::"enum_ReportResolutionStatus") THEN 1
                    ELSE 0
                END), (0)::bigint) AS ifm_pending_count
           FROM ("FraudCaseFiles" fcf_1
             LEFT JOIN "InterceptedFraudMessages" ifm ON ((ifm."fraudCaseFileId" = fcf_1.id)))
          GROUP BY fcf_1.id
        )
 SELECT fcf.id AS "FraudCaseFileId",
    fcf."CompanyId",
    fcf."createdAt",
    fcf."updatedAt",
    fcf.status,
    fcf."statusReason",
    fcf."reportedSource",
    ((COALESCE(rjc.rj_total_count, (0)::bigint) + COALESCE(sptj.spl_total_job_count, (0)::bigint)) + COALESCE(imc.ifm_total_count, (0)::bigint)) AS "totalReportCount",
    ((COALESCE(rjc.rj_pending_count, (0)::bigint) + COALESCE(sppj.spl_pending_job_count, (0)::bigint)) + COALESCE(imc.ifm_pending_count, (0)::bigint)) AS "pendingReportCount",
    COALESCE(imc.ifm_pending_count, (0)::bigint) AS "pendingInterceptedMessageCounts"
   FROM (((("FraudCaseFiles" fcf
     LEFT JOIN "ReportedJobCounts" rjc ON ((fcf.id = rjc."FraudCaseFileId")))
     LEFT JOIN "SystemPredictionTotalJobs" sptj ON ((fcf.id = sptj."FraudCaseFileId")))
     LEFT JOIN "SystemPredictionPendingJobs" sppj ON ((fcf.id = sppj."FraudCaseFileId")))
     LEFT JOIN "InterceptedMessageCounts" imc ON ((fcf.id = imc."FraudCaseFileId")));

FraudCaseFilesWithCountsView

Columns

Relationships

Close relationships within degrees of separation

View Definition

Possibly Referenced Tables/Views