时间戳

首页 > 解决方案 > How to extend dynamic schema with views in Hasura and Postgres?

postgresql - How to extend dynamic schema with views in Hasura and Postgres?

问题描述

So I am trying and struggling for few days to extend the schema with the custom groupby using something like this

I have a table with few fields like id, country, ip, created_at. Then I am trying to get them as groups. For example, group the data based on date, hourly of date, or based on country, and based on country with DISTINCT ip.

I am zero with SQLs honestly. But I tried to play around and get what I want. Here's an example.

 SELECT Hour(created_at) AS date,
COUNT(*) AS count
 FROM session where CAST(created_at AS date) = '2021-04-05'
 GROUP BY Hour(created_at)
 ORDER BY date;
 
SELECT country,
count(*) AS count from (SELECT * FROM session where CAST(created_at AS date) <= '2021-05-12' GROUP BY created_at) AS T1
GROUP BY country;
  
SELECT country, COUNT(*) as count
FROM (SELECT DISTINCT ip, country FROM session) AS T1
GROUP BY country;

SELECT DATE(created_at) AS date,
        COUNT(*) AS count
 FROM   session
 GROUP BY DATE(created_at)
 ORDER BY date;

Now I am struggling with two things.

  1. How do I make the date as variables? I mean, if I want to group them for a particular date range/ or today's data hourly, or per quarter gap (more of configurable), how do I add the variables in Hasura's Raw SQL?
  2. Also for this approach I have to add schema for each one of them? Like this
CREATE
OR REPLACE VIEW "public"."unique_session_counts_date" AS
SELECT
 date(session.created_at) AS date,
 count(*) AS count
FROM
 session
GROUP BY
 (date(session.created_at))
ORDER BY
 (date(session.created_at));

Is there a way to make it more generalized? What I mean is, if it was in Nodejs I could have done something like

return rawQuery(
   `
   select ${field} x, count(*) y
   from ${table}
   where website_id=$1
   and created_at between $2 and $3
   ${domainFilter}
   ${urlFilter}
   group by 1
   order by 2 desc
   `,
   params,
 );

In this case, based on whatever field and where clause I send, one query would do the trick for me. Can do something similar in hasura?

Thank you so much in advance.

标签： postgresqlhasura

解决方案

  1. How do I make the date as variables? I mean, if I want to group them for a particular date range/ or today's data hourly, or per quarter gap (more of configurable), how do I add the variables in Hasura's Raw SQL?

My first thought is this. If you're thinking about passing in variables via a GraphQL for example, the GraphQL would look something like:

query MyQuery {
    unique_session_counts_date(where: {created_at: {_gte: "<start date here>", _lte: "<end date here>"}}) {
        <...any fields, rollups, etc here...>
    }   
}

The underlying view/query would follow the group by and order by that you've detailed. Then you'd be able to submit a query of the graphql query and just pass in the pertinent parameters like the $1, $2, and $3 in the raqQuery call.

  1. Also for this approach I have to add schema for each one of them?

The schema? The view? I don't think a view specifically would be required, if a multilevel select or similar query can handle it and perform then a view wouldn't particularly be needed.

That's my first stab at the problem. I'm going to try to work through this problem in a few hours via a Twitch stream @ HasuraHQ if you can join, happy to walk through it live.

推荐阅读