Custom Query API

Avni allows you to save the implementation-related queries in the custom_query table. Right now, there is no UI, and the implementer needs to insert the query manually into the DB.

Sample insert statement

Adding new custom query

insert into custom_query (uuid, name, query, organisation_id, is_voided, version, created_by_id,
                          last_modified_by_id, created_date_time, last_modified_date_time)

values (uuid_generate_v4(),
        'Individual based on gender',
        'select concat(first_name, '' '', last_name) as "Full name",
               g.name as "Gender"
        from individual
                 join gender g on individual.gender_id = g.id
        where g.name = :gender
        and registration_date = cast(:date as date)
        limit 100;',
        21,
        false,
        0,
        1,
        1,
        now(),
        now());

Once the query is saved in the DB one can fire the post request to /executeQuery endpoint with the request body

Sample request body

{
    "name": "Individual based on gender",
   "queryParams": {
       "gender": "Male",
       "date": "2020-01-01"
   }
}

Below is the output response

Response

{
    "headers": [
        "Full name",
        "Gender"
    ],
    "data": [
        [
            "Hetram  Kewat Kewat",
            "Male"
        ],
        [
            "Reesham Kumar Panika",
            "Male"
        ],
        [
            "Ambika Prasad Panika",
            "Male"
        ],
        [
            "Shiv Prasad Panika",
            "Male"
        ],
        [
            "Aman Kumar Panika",
            "Male"
        ],
        [
            "Chandan Prasad Panika",
            "Male"
        ],
        [
            "Suraj Kumar Panika",
            "Male"
        ],
        [
            "Birendra Kumar Panika",
            "Male"
        ],
        [
            "Naresh Prasad Panika",
            "Male"
        ]
    ],
    "total": 9
}

Notes

1. Right now casts need to be handled in the query itself, for example in the above query casting of date param.