Description Problem:
I have problem finding correct way to transfer complex MySQL query to modX Revo. I know basics and can transfer simple queries but i'm stuck now when i need to deal with a query including all at a time: multiple joins, subqueries, several where clauses, limit&offset. Just can't find a way to put it all in one piece.
In pure MySQL it looks like that:
SELECT DISTINCT `id` FROM (
SELECT * FROM `site_model_news` AS A
LEFT JOIN ( SELECT `news`, `topic` FROM `site_model_newstopics` ) AS B
ON A.`id` = B.`news`
LEFT JOIN ( SELECT `user` as rssUser, `company` as rssCompany FROM `site_model_rss` ) AS C
ON A.`company` = C.`rssCompany`
WHERE B.`topic` IN ( 1,6 ) AND C.`rssUser` = 4
) AS D
WHERE D.`published`= 1
ORDER BY D.`created` DESC
LIMIT 10 OFFSET 0
I have a site with several companies that can post news, news can be assigned topics. User watching this site can add company to favorites and can set desirable topics. The posted query is used to retrieve a per-page list of news with chosen topics and posted by companies from favorites. User-company and news-topic relations are stored in database, userId (4), topics array (1, 6), limit and offset comes from php (the numbers used in query are just valid examples).
I will appreciate any tips on building correct xPDO Criteria for such query. Currently i have two major problems: subqueries in xPDO and using limit&offset after join (i keep getting less results than i should due to join duplicating rows).
- MODX: MODX Revolution 2.2.10-pl (traditional)
- PHP: 5.3.29
- Database: MySQL 5.5.40-36.1
