Templating queries to avoid view inefficiencies

[g105b]

Allow queries to import shared SQL templates for easier and more efficient reuse than using views.

select_customer.sql

select
  first_name,
  last_name,
  gender,
  id_customer,
  customer_type.name as name_customer_type

from customer

inner join customer_type
using(id_customer_type)

get_male_customers.sql

{select_customer}
where
  gender = "m"

Your thoughts, @j4m3s?

[g105b]

This is an example syntax of a view to achieve the same thing:

view_customer.sql

create view view_customer as
select
  first_name,
  last_name,
  gender,
  id_customer,
  customer_type.name as name_customer_type

from customer

inner join customer_type
using(id_customer_type)

get_male_customers.sql

select  * from view_customer
where
  gender = "m"

[j4m3s]

A View is the database solution to this problem of DRY/ query re-use. You wouldn't contemplate using templates like this in a php function, to avoid needing to cut & paste php code - you'd break it out into a function you can call from multiple places. Views are the db equivalent. (Views & SProcs).

Now, that said... I do cut & paste sql code from one file to another, keeping a master version of each snippet in a separate file somewhere. I do it because the MySQL query cache doesn't like Views. But in the cold hard light of day I bet I'm killing code maintenance for want of a millisecond or two here and there.

[g105b]

After testing the performance of concatenation/templating vs. views, the outcome is that views can be extremely slow compared to the copy-paste method or what would be introduced in this feature. Therefore my preference is to add this as a feature, but v2 (otherwise nothing will ever get completed!)

This repository introduces the organisation helper of QueryCollections, which would also be a nice way of organising the template files.

For example, the customer query collection can have a _get.sql, which can be included in getById.sql and getByName.sql using {get} syntax or @include get or whatever syntax we decide upon. Cross-collection templating could be done with {otherCollection/get} for instance.

[g105b]

Template queries should be stored in src/query/collection/_template to match the way pages are templated.

[g105b]

The functionality exposed in #124 surmounts the issues this idea faced. No solid ideas have been defined for template queries, so I am closing this issue with the intention of following on in #124.