Hi guys....
SQL/TV conundrum....
I am "trying" to write a SQL query to select some resources from the modx_site_content table based on some TV values.
I have 3 values that extend this resource that are TV’s:
tv_is_offer (1,0 with 1 being......I am an offer!!!) This is the key to the issue as im using this in my select.
tv_from_date
tv_to_date
So to pull all my resource offers I do something like:
SELECT * FROM modx_site_content, modx_site_tmplvar_contentvalues WHERE modx_site_content.id = modx_site_tmplvar_contentvalues.contentid
AND modx_site_tmplvar_contentvalues.tmplvarid = 7 AND value = 1
7 being the ID of tv_is_offer, and 1 being the flag.
Problem is I now want to ensure the dates are good, but can’t query them as the flag limits these lines?
So I need to ensure tv_from_date < todays date and tv_to_date > todays date and tv_is_offer = 1, can anyone lead their pR0 SQL skills?
Do you see my dilemma? I have a feeling there is a really simply solution...and i’m reading into this way to much.
-
☆ A M B ☆
- 3,141 Posts
I think you’ll need to use a MySQL join to do that, to join the site_content and site_tmplvar_contentvalues tables. But I’ve not used a query like that yet, so just guessing.
While you could probably use getResources for this query, can anyone chime in on how to do this via xPDO, I would be very grateful. I hit a wall trying to search on more than 1 TV (AND relationship). Also if it’s not possible, it would be great if someone could just say that...
As for your SQL above
SELECT * FROM modx_site_content resources, modx_site_tmplvar_contentvalues tvs WHERE resources.id = tvs.content id
AND tvs.tmplvar id = 7 AND tvs.value = 1
I can offer a few pointers. You should usually specify the context (table_name.row) when joining, and you can use aliases as I have done above(the word right after the real table name). When using "SELECT FROM table1, table2 ..." you create what I believe is called in MySQL a Natural Join. In my experience these are very weak joins. I believe they rely on columns in tables being identical. At best I have found them to be unpredictable and unreliable. I always get better results using a specific type of join (left, right, inner, outer).
Anyone else lend a hand?
MODx 2.0.6
PHP 5.3.3
MySQL 5.1.50
Hi guys....
Should have mentioned that im running Evo not revo, so I have to use the old DBAPI, which is not the problem.
In the SQL I have provided there is a join, however once I select the flag row to filter the content I can’t then query the other 2 tv’s to validate my dates....
hmmmmmmm?
