there is a function I have written for bloX which generates a sql-query from given parameters for blox to get documents and tvs in with one query.
see
http://github.com/Bruno17/bloX-Xedit/blob/master/snippets/blox/inc/blox.class.inc.php
about line 1193
if you call bloX for example like that:
[!bloX? &fields=`pagetitle,longtitle,myfirstTV,mysecondTV`&filter=`myfirstTV|test|%LIKE%++mysecondTV|200|gt`&parents=`15,45`&orderBy=`menuindex`!]
the function getdocs generates together with the function filter2sqlwhere and other helperfunctions the needed sql-statement for this.
of course this cannot get processed TV-output with bindings, but gets also default-values, if value is empty
It seems it’s what I need.
Thank you.
if I call
[!bloX? &fields=`location,code`!]
it generates:
SELECT distinct IF(location_tvcv.value!='',location_tvcv.value,location_tv.default_text) AS location , IF(code_tvcv.value!='',code_tvcv.value,code_tv.default_text) AS code , sc.id , sc.pagetitle , sc.published FROM (`d00c44c7`.`modx_site_content` AS sc,`d00c44c7`.`modx_site_tmplvars` AS location_tv ,`d00c44c7`.`modx_site_tmplvars` AS code_tv ) LEFT JOIN `d00c44c7`.`modx_site_tmplvar_contentvalues` AS location_tvcv ON location_tvcv.contentid = sc.id AND location_tvcv.tmplvarid = location_tv.id LEFT JOIN `d00c44c7`.`modx_site_tmplvar_contentvalues` AS code_tvcv ON code_tvcv.contentid = sc.id AND code_tvcv.tmplvarid = code_tv.id WHERE 1 AND location_tv.name = 'location' AND code_tv.name = 'code' AND sc.`parent`IN(9999999) AND sc.`published`="1" AND sc.`deleted`="0" ORDER BY sc.menutitle
-
☆ A M B ☆
- 2,475 Posts
I like getting things in one query if I can, but getting the TVs for documents can make for some insanely difficult MySQL JOIN statements, and as frustrating as it might seem, it might be much much easier and faster to use multiple queries to assemble your data. Just a thought.... the proof is in the pudding.
but... if you want to filter, sort, limit (for pagination for example), have mysql-genarated-fields and so on IMO its easier and better to have it all in one query, instaed to get the whole table and filter out and sort and so on in foreach-loops.
that was the reason to create this functions for bloX, which generates the needed mysql-statement from given parameters &filter &orderBy &fields &groupBy &parents &documents &perPage &pageStart
For me its 1 query vs 501. I have 500 entries that I need to show in a table.
Now that I know how to do it it’s easy.
-
☆ A M B ☆
- 2,475 Posts
Yeah, there’s an art to getting everything in as few queries as possible. I’ve had to write logic at times that would do 3 queries:
1. Select the IDs that matched the criteria in the order that they needed to be returned (i.e. only 10 results per page)
2. Select the data from those IDs
3. Count the total number of records for proper pagination.
I haven’t formalized the structure that emerged, but I could see it was definitely a pattern that would recur for complex queries.
