Show Corteza: Raw SQL Views In Reports

[shmuel]

This is definitely “advanced usage only” and neither reports nor modules were intended to be used in this way, but with the new physical table functionality and some minor hackery, you can now use SQL views inside of the reports builder.

Steps

1. Create a module with the correct fields and module name. These will be the fields for the view. Remove all of the ancellary default and audit fields like recordId and createdAt.
2. In the DB, drop the table
3. Create a view with the same field types and field names from step 1. Make sure the view name matches the module handle
4. Add a single load in the report builder and you’re done

Proofs

Here are the child records

Here’s the view definition

CREATE VIEW "ChildAggView" AS
SELECT
    SUM("Amount") "TotalAmount", p."Name" "Parent"
FROM "Child"
JOIN "Parent" p on p.id = "Child"."Parent"
GROUP BY p."Name";

And here’s the report with the single load!

[AndreB]

Nice this is very interesting and we are really happy with the way you are experimenting with Corteza! Our developers and community will definitely find this useful so thank you.

[vishva]

Hello,

I tried this steps but it is not working for me. throwing these error : pq: column week_day_view.values does not exist

[tjerman]

Did you configure the fields to point to the correct columns of your view? When editing a module, under the data store tab; you can configure where the data comes from.

[vishva]

Thank you for the reply,

yes please find the screen shot

also the details are now available but are blank

but in the database there is the data available in view

[tjerman]

Well… it’s not configured correctly…
See your values, it’s NULL. The two columns you want are weekday and inquiry_count; change those two selects to column and it should work

[vishva]

Thanks @tjerman it worked!