# Strapi Populate Gen using sqlite - Populate Query Automation

## Generate FinalQuery2 as view

--
--	select  pageComponents.TableNameSegment from pageComponents;
Select 

*,
REPLACE(
REPLACE(
REPLACE('SELECT q{id}.* FROM (Select field as Field_{fieldName}, ''&populate={fieldName}.'' || GROUP_CONCAT(field, ''&populate={fieldName}.'') as ''{fieldName}_Field''  
   FROM (select distinct field from {tableName})) as q{id}', 
	 '{fieldName}', q2.Field), 
	 '{tableName}', q1.TableName), 
	 '{id}', q1.tableId)
	 as QA

FROM (SELECT
  rootpage as tableId,
	tbl_name as TableName,
	( SELECT GROUP_CONCAT( name, ',' ) FROM PRAGMA_TABLE_INFO ( tbl_name ) ) AS Columns,
	'Select distinct field FROM ' || name AS 'Query'	
FROM
	sqlite_master 
WHERE
	type = 'table' 
	AND name LIKE '%components%' 
	AND name NOT LIKE 'sqlite_%' 
	AND columns LIKE '%field%') as q1,
	(Select Field,
REPLACE(REPLACE(component_type,'-','_'),'.','_') as TableNameSegment
from pages_components ) as q2
where q1.TableName like '%' || q2.TableNameSegment || '%'

## Gen Query `GenQuery` view

SELECT
	( q1.sqlField ) 
FROM
	( SELECT GROUP_CONCAT( QA, ';' || char ( 13 ) || char ( 10 ) ) AS sqlField FROM FinalQuery2 ) AS q1

## Finally execute all sql query from the genquery

SELECT q120.* FROM (Select field as Field_Seo, '&populate=Seo.' || GROUP_CONCAT(field, '&populate=Seo.') as 'Seo_Field'  
   FROM (select distinct field from components_seo_seos_components)) as q120;
SELECT q79.* FROM (Select field as Field_Hero, '&populate=Hero.' || GROUP_CONCAT(field, '&populate=Hero.') as 'Hero_Field'  
   FROM (select distinct field from components_hero_sections_home_heroes_components)) as q79;
SELECT q209.* FROM (Select field as Field_Body, '&populate=Body.' || GROUP_CONCAT(field, '&populate=Body.') as 'Body_Field'  
   FROM (select distinct field from components_section_blocks_icons_title_subtitle_feature_image_sections_components)) as q209;
SELECT q236.* FROM (Select field as Field_Body, '&populate=Body.' || GROUP_CONCAT(field, '&populate=Body.') as 'Body_Field'  
   FROM (select distinct field from components_section_blocks_key_value_rich_text_pairs_sections_components)) as q236

## References

* [c++ - How to insert a new line ("\n") character in SQLite? - Stack Overflow](https://stackoverflow.com/questions/47227684/how-to-insert-a-new-line-n-character-in-sqlite)
* [sql - Declare variable in SQLite and use it - Stack Overflow](https://stackoverflow.com/questions/7739444/declare-variable-in-sqlite-and-use-it)
* [SQLite REPLACE Function By Practical Examples](https://www.sqlitetutorial.net/sqlite-replace-function/)
* [sql - Declare variable in SQLite and use it - Stack Overflow](https://stackoverflow.com/questions/7739444/declare-variable-in-sqlite-and-use-it)
* [Population for REST API - Strapi Developer Docs](https://docs.strapi.io/developer-docs/latest/developer-resources/database-apis-reference/rest/populating-fields.html#component-dynamic-zones)

## Strapi Query Sample

http://localhost:1337/api/pages?filters[Slug][$eq]=home-1&populate=*&populate=Hero.AppStoreImagesLinks&populate=Hero.AppStoreTitle&populate=Hero.Appearance&populate=Hero.CarouselImages&populate=Hero.ConverterButtonsList&populate=Hero.FeatureImage&populate=Hero.Header&populate=Hero.ReviewSectionTitle&populate=Hero.Reviews&populate=*&populate=Seo.AppStoreImagesLinks&populate=Seo.AppStoreTitle&populate=Seo.Appearance&populate=Seo.CarouselImages&populate=Seo.ConverterButtonsList&populate=Seo.FeatureImage&populate=Seo.Header&populate=Seo.ReviewSectionTitle&populate=Seo.Reviews&populate=Body.Appearance&populate=Body.Header&populate=Body.KeyRichValues&populate=Body.Appearance&populate=Body.Header&populate=Body.Icons&populate=Seo.MetaAttributes&populate=Seo.Metas