get all fields and all TVs with one query

1,624 Posts

cipa Reply #1, 17 years, 3 months ago

Hi,

I’m looking for a way to get all the TVs and document fields in one SQL query.

I would like the sql output to look like

id | pagetitle | longtitle .... tv1 | tv2 | tv3 | tv4 |
3 | test | test .... 1 | 2 | 3 | 4 |
....

Off course I will customize the query to my needs.

Thx

Responsive CSS Grid: cipalabs.com/projects/metal/
Blog: MODX Tutorials & Articles

☆ A M B ☆
2,475 Posts

Everett Reply #2, 17 years, 3 months ago

There may be a built-in API for this, but in raw SQL, it’s a bit messy. I have something like this for one query... I had to identify the tmplvarid for each template variable.

SELECT  
    page.pagetitle
    , page.id
    , page.longtitle
  /* [...] */
FROM site_content as page 
LEFT JOIN site_tmplvar_contentvalues as releasedate ON (page.id=releasedate.contentid AND releasedate.tmplvarid='6')
LEFT JOIN site_tmplvar_contentvalues as director ON (page.id=director.contentid AND director.tmplvarid='7')
/* [...] */
WHERE
    page.published='1'

Hosting stuff: http://fireproofsocks.com/
Coding stuff: http://craftsmancoding.com/
Blog stuff: http://tipsfor.us/

2,877 Posts

mrhaw Reply #3, 17 years, 3 months ago

To quickly find out what resources being used, the default manager search and
doc finder module (extras) works nice. But it sure would be nice with a dynamic summary.

@hawproductions | http://mrhaw.com/

Infograph: MODX Advanced Install in 7 steps:
http://forums.modx.com/thread/96954/infograph-modx-advanced-install-in-7-steps

Recap: Portland, OR (PDX) MODX CMS Meetup, Oct 6, 2015. US Bancorp Tower
http://mrhaw.com/modx_portland_oregon_pdx_modx_cms_meetup_oct_2015_us_bancorp_tower