klewas VIP
Total posts: 18
09 Apr 2014 22:07

Hello, I need some advice. I have many records in the #__js_res_record table and I need to find all the records which have some particular field value. Now I am using

SELECT 'id, title' FROM #__js_res_record WHERE 'type_id' = $type_id AND 'fields' LIKE '%{"'.$id.'":"'.$name.'"}%'

but I am not sure if this is proper and fastest way. I believe you have some classes for similar tasks, because pre-filtered links works in that way, but I didn't find the proper method yet..

Last Modified: 10 Apr 2014


Sackgesicht VIP
Total posts: 1,636
10 Apr 2014 00:29

It depends what you want to achieve (render the result, or just get a record list). If you want to display a list of records, you can use a pre-filtered link (like you mentioned) already. Another way to do it, is the "Render an article list" through an api call. See documentation here.

To get a list of matching ID's, i would query the #js_res_record_values table based on the field_key and field_value. This query will be much faster than querying the #_js_res_record with a "LIKE" statement.


Sergey
Total posts: 13,748
10 Apr 2014 06:25

Example of what Sackgesicht is suggesting may be like this

SELECT id, title FROM #__js_res_record 
 WHEER id IN(
    SELECT record_id FROM #__js_res_record_values
     WHERE field_value = '{$name}'
 );

klewas VIP
Total posts: 18
10 Apr 2014 06:40

Thanks, it's great:) Somehow i didn't noticed the #__js_res_record_values table.

Powered by Cobalt