Category_id not saved in table js_res_record_values ?

Quote

Total posts: 745
12 Oct 2015 12:17

Hi,

I recently checked js_res_record_values and it appears that all category_id values are 0. Type_id and section_id do have values though.

Is this a bug?

Best regards, Jeff

Last Modified: 19 Oct 2015

Quote

Sergey

Total posts: 13,748
13 Oct 2015 20:49

No. I used to plan to store there category id but since one article can be in few categories it was not possible to assosiate field value with category.

Quote

Jeff

Total posts: 745
13 Oct 2015 23:14

Ah, ok.

The only problem is that I don't know how to filter a specific category with the following query

$sql = "SELECT record_id FROM #__js_res_record_values WHERE (field_id = 3 OR field_id = 23) AND
DATE_ADD(field_value, 
                INTERVAL YEAR(CURDATE())-YEAR(field_value)
                         + IF(DAYOFYEAR(CURDATE()) > DAYOFYEAR(field_value),1,0)
                YEAR)  
            BETWEEN CURDATE() AND DATE_ADD(CURDATE() , INTERVAL 7 DAY)";

Quote

pepperstreet VIP

Total posts: 3,837
14 Oct 2015 01:20

I believe, category is stored in table #__js_res_record, isn't it? And you might need to join.

Quote

Jeff

Total posts: 745
14 Oct 2015 01:49

pepperstreet I believe, category is stored in table #__js_res_record, isn't it? And you might need to join.

Correct....I'm just no MySql wizard.

Quote

Jeff

Total posts: 745
14 Oct 2015 03:21

Got it!

$sql = "SELECT a.record_id, b.id FROM #__js_res_record_values a, #__js_res_record b

        WHERE (a.field_id = 3 OR a.field_id = 23) 
        AND a.record_id = b.id
        AND b.categories LIKE '%Category%'
        AND DATE_ADD(field_value, 
                INTERVAL YEAR(CURDATE())-YEAR(field_value)
                         + IF(DAYOFYEAR(CURDATE()) > DAYOFYEAR(field_value),1,0)
                YEAR)  
        BETWEEN CURDATE() AND DATE_ADD(CURDATE() , INTERVAL 7 DAY)";

Quote

Sergey

Total posts: 13,748
16 Oct 2015 12:58

You want records from specific categories?

SELECT a.record_id, b.id FROM #__js_res_record_values a

    WHERE (a.field_id = 3 OR a.field_id = 23) 
    AND a.record_id = b.id
    AND DATE_ADD(field_value, 
            INTERVAL YEAR(CURDATE())-YEAR(field_value)
                     + IF(DAYOFYEAR(CURDATE()) > DAYOFYEAR(field_value),1,0)
            YEAR)  
    BETWEEN CURDATE() AND DATE_ADD(CURDATE() , INTERVAL 7 DAY)
    AND record_id IN (SELECT c.record_id FROM #__js_res_record_category AS с WHERE c.catid IN(2,3,4))

Quote

Jeff

Total posts: 745
17 Oct 2015 01:10

Ah ok,

but then it should be

SELECT record_id FROM #__js_res_record_values

    WHERE (field_id = 3 OR field_id = 23) 
    AND DATE_ADD(field_value, 
            INTERVAL YEAR(CURDATE())-YEAR(field_value)
                     + IF(DAYOFYEAR(CURDATE()) > DAYOFYEAR(field_value),1,0)
            YEAR)  
    BETWEEN CURDATE() AND DATE_ADD(CURDATE() , INTERVAL 7 DAY)
    AND record_id IN (SELECT a.record_id FROM #__js_res_record_category AS a WHERE a.catid IN(2,3,4))

Quote

Sergey

Total posts: 13,748
19 Oct 2015 07:15

O yes, I'he overlooked that one.

Login (copy)