Bubble App - SQL Complex Views

Hey All!,

I have a LOCAL SQL server with relatively complex views performing JOINS and various calculations based on the database schema.

13 Dimension Tables
1 Fact Table
9 SQL Views.

I have begun building the front end in Bubble.

Herein lies my problem,

I would like to maintain the logic behind the SQL views in my bubble app

Im not confident that Bubble can replicate my SQL Views.
Unless proven wrong?

I am contemplating using the API - To update my Bubble data- and possibly developing a C Sharp App with a form to update the backend.

I’ve used the following potential solutions with my experience

  • native Bubble SQL connector which requires an online SQL database.
    slow and buggy

  • MS Azure - uploaded everything to the online Azure SQL server at a ridiculous cost.
    I want to avoid using online cloud hosting UNLESS a cost-effective solution can be provided for less than AUD 50 a month.

  • Played around with NoSQL but unclear if NoSQL can replicate the same logic behind my SQL Views.

Below is only one of the SQL views to gain an understanding of the logic I am working with

OPEN TO SUGGESTIONS!

WITH CalculateTotal AS
(
    SELECT
        aa.Class,
        aa.[Year],
        da.DataAgreementID,
        (ss.[Superannuation Actual] + aa.Total_All_Sums + wage.TotalWages + allow.AllowanceTotal) AS [total]
    FROM
        Fact_DataAgreement da
        INNER JOIN [dbo].[Dim_OnCosts] oc ON da.DataAgreementID = oc.DataAgreementID
        INNER JOIN vw_superannuation_sum ss ON ss.DataAgreementID = da.DataAgreementID
            AND ss.[Year] = YEAR(oc.beginDate)
        INNER JOIN [dbo].[vw_accruals_Sum] aa ON aa.DataAgreementID = oc.DataAgreementID
            AND aa.Class = ss.class
            AND aa.[Year] = YEAR(oc.beginDate)
        INNER JOIN [dbo].[vw_Allowance_sum] allow ON allow.DataAgreementID = oc.DataAgreementID
            AND allow.class = ss.class
            AND allow.[Year] = YEAR(oc.beginDate)
        INNER JOIN [dbo].[vw_Wages_sum] wage ON wage.DataAgreementID = oc.DataAgreementID
            AND wage.class = ss.class
            AND wage.[year] = YEAR(oc.beginDate)
    GROUP BY
        aa.Class,
        aa.[Year],
        (ss.[Superannuation Actual] + aa.Total_All_Sums + wage.TotalWages + allow.AllowanceTotal),
        da.DataAgreementID
),
CalculateRedundancy AS
(
    SELECT * FROM
    (
        SELECT
            YEAR(oc.beginDate) AS [Year],
            CASE
                WHEN ispercent = 0 AND oc.[name] = 'Redundancy  Contribution' THEN oc.[No of Units Per Annum] * oc.Amount
            END AS [RedundancyContribution]
        FROM
            [ConstructionMasterAccessExport].[dbo].[Dim_OnCosts] oc
    ) a
    WHERE
        a.RedundancyContribution IS NOT NULL
),
CalculateWorkersPercent AS
(
    SELECT * FROM
    (
        SELECT
            YEAR(oc.beginDate) AS [Year],
            oc.[No of Units Per Annum] AS [Percent]
        FROM
            [ConstructionMasterAccessExport].[dbo].[Dim_OnCosts] oc
        WHERE
            oc.IsPercent = 1
			and oc.name like '%Workers%'
    ) a
),
CalculateInsur AS
(
    SELECT * FROM
    (
        SELECT
            YEAR(oc.beginDate) AS [Year],
            CASE
                WHEN ispercent = 0 AND oc.[name] = 'Insur top-up & sick ' THEN oc.[No of Units Per Annum] * oc.Amount
            END AS [Insur top up]
        FROM
            [ConstructionMasterAccessExport].[dbo].[Dim_OnCosts] oc
    ) a
    WHERE
        a.[Insur top up] IS NOT NULL
)
,
CalculatePayrollPercent AS
(
    SELECT * FROM
    (
        SELECT
            YEAR(oc.beginDate) AS [Year],
            oc.[No of Units Per Annum] AS [Percent]
        FROM
            [ConstructionMasterAccessExport].[dbo].[Dim_OnCosts] oc
        WHERE
            oc.IsPercent = 1
			and oc.name like '%Payroll%'
    ) a
)
SELECT
    ct.[Year],
    ct.Class,ct.DataAgreementID,
	sum(
    cp.[Percent] * (ct.total + cr.RedundancyContribution)+
	cpp.[Percent] * (ct.total + cr.RedundancyContribution)+
	 cr.RedundancyContribution + ci.[Insur top up] )
	 as [Total]
  --cp.[Percent] * (ct.total + cr.RedundancyContribution), + cr.RedundancyContribution + ci.[Insur top up] AS [Result]
FROM
    CalculateTotal ct
    INNER JOIN CalculateRedundancy cr ON cr.[Year] = ct.[Year]
    INNER JOIN CalculateInsur ci ON ci.[Year] = ct.[Year]
    INNER JOIN CalculateWorkersPercent cp ON cp.[Year] = ct.[Year]
	INNER JOIN CalculatePayrollPercent cpp ON cpp.[Year] = ct.[Year]
	group by 
	    ct.[Year],
    ct.Class,ct.DataAgreementID```

Dude. If you’re comfortable in sql, please just go use supabase and to help with the bubble backend. It’s really nice, powerful, and convinient but it doesn’t trump a traditional PostgRES db with the ability to execute views, functions, etc. table joins are a joke on Bubble unfortunately.

1 Like

Thankyou I will consider this