1

I try to create a query to get all news items, that are flagged with at least two different categories and they have to match the AND clause.

I need this query to make a decision, if the following code should be rendered, or not. E.g. If there is no news item with category A and category B, do nothing. Else show tx_news LIST view.

lib.field_dmnewsplugin.5 = CONTENT 
lib.field_dmnewsplugin.5 {
    table = tx_news_domain_model_news
    select {
        pidInList = 124
        max = 9
        orderBy = uid DESC

        leftjoin = sys_category_record_mm ON (sys_category_record_mm.uid_foreign = tx_news_domain_model_news.uid)
        #andWhere = sys_category_record_mm.uid_local IN (14,16)
        #where = sys_category_record_mm.uid_local = 14
        andWhere = sys_category_record_mm.uid_local = 14 AND sys_category_record_mm.uid_local = 16
    }
    renderObj = COA
    renderObj {
        1 = TEXT
        1.value = Aktuelles
        1.wrap = <h2>|</h2>
        2 = TEXT
        2.field = title
        2.crop = 50|...|1
        2.wrap = <h3>|</h3>
        3 = TEXT
        3.field = teaser
        3.crop = 500|...|1
        3.wrap = <p>|</p>
        }
    }

My code is the result of some testings. With the "andWhere" clause, the result is empty. without any where clause, I get double entries for all news items, because all of them have at least two different categories. My goal is to get unique results for each news item, that is flagged with category A and category B (and maybe as an universal solution additional categories).

What do I have to do?

Thank you in advance,

Ralf

Waimanu
  • 11
  • 1

2 Answers2

0

Try to put the WHERE clause into the ON part of the JOIN and use a groupBy to get a counter.

select {
    selectFields = count(*) AS counter
    leftjoin = sys_category_record_mm ON (sys_category_record_mm.uid_foreign = tx_news_domain_model_news.uid) AND sys_category_record_mm.uid_local IN (14,16)
    pidInList = 124
    max = 9
    groupBy = uid
    orderBy = uid DESC
    where = counter > 1
}
Jo Hasenau
  • 2,526
  • 1
  • 14
  • 16
  • Thank you very much for your suggestion! I understand, what you mean. But now, I get the following error: "Unknown column 'counter' in 'where clause'" Any idea, how I can get rid of that? Thanks in advance, Ralf – Waimanu Feb 14 '19 at 15:56
  • By the way. Without the where clause, I get results. I render the counter and it shows the number of added categories 1 or 2. The items with counter = 2 are these I would like to see. But, of course, without a working filter, it shows all entries. – Waimanu Feb 14 '19 at 16:09
0

After I had to realize, that Jo's solution does not work for me, I had another idea:

lib.field_dmnewsplugin = COA
    lib.field_dmnewsplugin {
        10 = CONTENT 
        10 {
            table = tx_news_domain_model_news
            select {
                selectFields = title, teaser, count(uid) AS counter
                leftjoin = sys_category_record_mm ON (sys_category_record_mm.uid_foreign = tx_news_domain_model_news.uid) AND sys_category_record_mm.uid_local IN ({14,###maincat###)
                pidInList = 124
                max = 1
                groupBy = uid
                orderBy = counter DESC, crdate DESC
                #where = counter > 1
                markers {
                    maincat.value = 16
                    }
                }
            renderObj = COA
            renderObj {
                10 = COA
                10 {
                    stdWrap {
                        if {
                            value = 1
                            isGreaterThan.data = field:counter
                            #equals.data = field:counter
                            }
                        required = 1
                        wrap = <h2>Some Headline</h2>
                        }
                    10 = USER
                    10 {
                        userFunc = TYPO3\CMS\Extbase\Core\Bootstrap->run
                        extensionName = News
                        pluginName = Pi1
                        vendorName = GeorgRinger

                        switchableControllerActions {
                            News {
                                1 = list
                                }
                            }
                        settings < plugin.tx_news.settings
                        settings {
                            cropMaxCharacters = 164 | &nbsp;[...] | 1
                            categoryConjunction = and
                            categories = 14,16
                            excludeAlreadyDisplayedNews = 1
                            archiveRestriction = active
[...]

The problem is, that we cannot use the alias "counter" in the where clause and I have no idea, how I can solve the problem with typoscript. With native SQL there might be a better way.

But I'm able to get the value of "counter" to create an "if" rule. And additionally, I can sort the query by "counter". So, if the query returns at least one hit with "counter" greater than 1, I can decide to render a COA-Object like a news list view with headline.

I'm satisfied with this solution. But maybe, somebody has a special trick for me?

Thank's for your help,

Ralf

Waimanu
  • 11
  • 1