Need help with selecting document based on TV value.

3 Posts

cooperaj Reply #1, 18 years, 11 months ago

I’m in the process of creating a snippet to handle a Calendar on my website. I’ve taken a look at the few that are available (DittoCal, CALx, Kalendar etc) and although CALx was the closest to what I wanted I was unable to successfully grep the code to make it do what I want. French comments not withstanding it seemed that it used flatfiles to store info?

Using PHP calendar I’ve managed to knock together something that displays a calendar that you can navigate forwards and backward with. In about 120 lines as opposed to CALx’s 1600.

I’ve come to the stage where I want to get a list of events for a month. I need to get a list of the children of a document that have an event date between startdate and enddate. The function getChildrenTemplateVars() gets me a list of the childrens TVs i’m interested in (startdate, enddate). I could then use PHP to go through all the returned values and pick the ones the fall in the right date range. But I don’t want to do that. I want a function that will do all this via SQL not as PHP.

Surely theres a way to return what I need using an SQL statement as opposed to a number of embedded PHP loops.

After this issue’s solved I need to create a method of templating the list of events for a certain month/day. I envisage something similar to ditto whereby each event gets output using placeholders into an event entry template. Pointers?

Or am I going about this the completely wrong way? Take a look at modx.devonshireavebaptist.org to see where I am so far.

Regards from a 4 day newb.
Adam

3 Posts

cooperaj Reply #2, 18 years, 11 months ago

Anybody?

I’ve found out all about the great placeholder functionality and am using it in some glue code snippets so that part is ok. I just need info on how to query for documents that have

a) A specific date value set (startDate) as a TV
b) A date value that falls within the given range. Be it a day or a month.

i.e. A document has the TVs set as;
startDate: 2007/10/28 15:00
endDate: 2007/10/28 19:00

The query asks for all documents that have the startDate TV set and that that TV falls in the range 2007/10/01 00:00 to 2007/10/31 23:59

I’m sure it can be done as a largish SQL query but I don’t have the experience with the modx schema to be able to put it together. Any hints?

Thanks
Adam

3 Posts

cooperaj Reply #3, 18 years, 11 months ago

Well, after much futzing around in PHPMyAdmin I’ve managed to create this:

SELECT DISTINCT docs.id
FROM `site_content` docs
INNER JOIN `site_tmplvar_contentvalues` tvcontent
 ON docs.id = tvcontent.contentid
INNER JOIN `site_tmplvars` tv 
 ON tvcontent.tmplvarid = tv.id
WHERE docs.parent = '2' AND docs.published = '1'
 AND (STR_TO_DATE(tvcontent.value, '%d-%m-%Y %H:%i:%s') BETWEEN 
   '2007-10-01 00:00:00' AND '2007-12-30 23:59:59') 
 AND(tv.name = 'startDate' OR tv.name = 'endDate')

Seems to do roughly what I need. Though it may undergo a few changes.

One question? Why the daft date format? It does not adhere to any ISO standard or country standard and caused me no end of confusion.

Thanks
Adam