Author Topic: sql queries templating (to handle params) (Read 1047 times)

xavier · June 17, 2014, 11:39:53 pm

Tim suggested to have a self contained file containing both the sql query and defining what params are allowed (and their types)
http://forum.civicrm.org/index.php/topic,33165.msg141121.html#msg141121

It might be as well needed to have different queries based on a parameter. as an example for ep2014, I could either get all the candidates that signed a specific pledge (so join on group and where group=%groupid) or all candidates (so no join on group, but where contact_sub_type like "%candidate%")

I'm wondering if adding more cleverness to a mysql.tpl is the best way, shouldn't we simply allow {crmsql} to call a php function, and that function gets all the params. Basically what {crmAPI} does for the api, but it returns a sql instead of the list of results.

I'm looking for ideas for improving the implementation compared to what we have done for api (and who knows, use it as playground for api4)

1) I want to use Tim new sql builder magic, but not sure: is this in the core already? Can we use it for that?

2) instead of relying on a naming convention (function civicrm_api3_entity_action is into the file Entity.php...) can't we use something different? I somehow think about the file returning an anonymous function, but not quite sure how it's going to help

Might be simplier to simply define a namespace per extension and simply have a class that implements the query+param handling

X+

totten · June 18, 2014, 02:08:48 am

I'm responding a little out-of-order. Sometimes out-of-order execution is more efficient...

Quote from: xavier on June 17, 2014, 11:39:53 pm

I'm wondering if adding more cleverness to a mysql.tpl is the best way, shouldn't we simply allow {crmsql} to call a php function, and that function gets all the params. Basically what {crmAPI} does for the api, but it returns a sql instead of the list of results.

...

1) I want to use Tim new sql builder magic, but not sure: is this in the core already? Can we use it for that?

I'm not sure what this would look like, but yes -- yes, the query-builder was released with 4.4.5 -- https://github.com/civicrm/civicrm-core/blob/4.4.5/CRM/Utils/SQL/Select.php . It's also a single-file, so if you backward-compat, you could bundle it with an extension and devise some conditional loading.

Quote from: xavier on June 17, 2014, 11:39:53 pm

I'm looking for ideas for improving the implementation compared to what we have done for api (and who knows, use it as playground for api4)

...

2) instead of relying on a naming convention (function civicrm_api3_entity_action is into the file Entity.php...) can't we use something different? I somehow think about the file returning an anonymous function, but not quite sure how it's going to help

If we're talking about Civi v4.5+ (with API kernel), then there are a lot of things we could do. Some off-the-wall ideas (with no particular filtering for good/bad):

Code: [Select]

// IDEA 1: Each named query is its own API. APIs are defined in Smarty using basic SQL templates.
// +
// IDEA 2: Put all the named-queries in a single tpl file. 

// FILE: com.example.mymodule/templates/myvisualization.tpl
var data = {crmAPI entity=Foo action=bar prefix_id=3};

// FILE: com.example.mymodule/api/queries.tpl
{sqlAPI entity="Foo" action="bar}
  {sqlVar name="prefix_id" type="integer"}
  {sqlQuery} SELECT count(*) from civicrm_contact where prefix_id = {$prefix_id} {/sqlQuery}
{/sqlAPI}
{sqlAPI entity="Foo" action="whiz}
  {sqlVar name="contact_type" type="string"}
  {sqlQuery} SELECT count(*) from civicrm_contact where contact_type = {$contact_type} {/sqlQuery}
{/sqlAPI}

Code: [Select]

// IDEA 3: Each named query is its own API. APIs are defined in json.
// +
// IDEA 4: Use query-builder. Define baseline (that's always part of the query)
// and define white-list of options (group-by, where, etc) that can be
// chosen by API caller.

// FILE: template/my-visualization.tpl
var data = {crmAPI entity=Activity action=getsql groupby="c.contact_type,a.activity_type_id"};

// FILE: api/v4/Activity/getsql.sql-json
{
  permission: "access all contacts",
  select: "count(*)",
  from: "civicrm_contact c",
  join: [
   "inner join civicrm_activity_contact ac on ac.contact_id = c.id",
   "inner join civicrm_activity a on a.id = ac.activity_id"
  ],
  allow_group_by: ["c.contact_type", "c.contact_sub_type", "a.activity_type_id", "month(a.activity_date_time)"],
  allow_where: ["c.contact_type", "c.contact_sub_type", "a.activity_type_id", "month(a.activity_date_time)"]
}

xavier · June 18, 2014, 05:05:52 am

I like the direction it's getting. I'm warming up to the api idea, but still not 100% sold yet.

It seems we are mixing two different issues (param for sql with the select builder and permission). I'm looking at the first one: what about having

query/activities.json that contains:

Code: [Select]

{
  select: "count(*)",
  from: "civicrm_contact c",
  join: [
   "inner join civicrm_activity_contact ac on ac.contact_id = c.id",
   "inner join civicrm_activity a on a.id = ac.activity_id"
  ],
  group_by: "c.contact_type, c.contact_sub_type,a.activity_type_id,month(a.activity_date_time)",
  where:  ["c.contact_type = @contact_type"]
}

and be used from

Code: [Select]

{crmsql jsonfile=activities contact_type="Individual"}
?

Eileen · June 18, 2014, 02:12:18 pm

X, my main argument for an api-centric approach is that, at least on contact api, the api respects ACLs as well as permissions (if you pass the check_permissions flag). I think it's inevitable we will add acls to the participants & contributions api - so if you are leveraging the api you can leave adding ACLs to your queries & treat it as something that 'will come when added to the respective api parts'

s0014 · July 14, 2014, 09:16:07 am

Hey,

Can someone please help me with http://forum.civicrm.org/index.php/topic,33537.0.html.

CiviCRM Community Forums (archive)

News:

Author Topic: sql queries templating (to handle params) (Read 1047 times)

xavier

sql queries templating (to handle params)

totten

Re: sql queries templating (to handle params)

xavier

Re: sql queries templating (to handle params)

Eileen

Re: sql queries templating (to handle params)

s0014

Re: sql queries templating (to handle params)