Retrieve a field value in SQL query

Hello narcis,
see the last SQL example in the following documentation article. Hence the line with CONCAT command and usage of field_id ... it selects field data from the Record's values table (#__js_res_record_values)

• SQL values source

Thank you pepperstreet. I will take a look.

You want to get valued of particular field? We are talking aboutt Select field?

You have number of options. It really depends where you are to make a suggestion, are you in template, field template, custom field, ... But in basic you can.

1. SQL query tp #__js_res_record_values for a field_value wheer field_id and record_id are what you need.
2. If you have access to record object then you can $item->fields[11] where 11 is an ID of the field
3. If $item->fields is a string make $item->fields = json_decode($item->fields, TRUE) before
4. If you do not have record object you can get it like so $item = ItemsStore::getRecord(20) where 22 is an id of the record.

Hello Sergey,

Actually i want to get the value of particular field in custom field. I think it'd be nice to get a changed value there, because the particular field is always edited. I tried the Sql query as mentioned in doc, but :

1- Value is not changed in custom field when the particular field value changes

2- it does not work when particular field is a Checkbox one. I get this error message :

Error: 1242 Subquery returns more than 1 row SQL=SELECT r.id, CONCAT(r.title, ' Rewards: ', (SELECT field_value FROM xxx_js_res_record_values WHERE record_id = r.id AND field_id IN(34,54,12,13)) ,'') AS text FROM xxx_js_res_record AS r WHERE r.section_id IN(1,2) AND r.user_id = 45

I have some additional questions :

All above is temporary. Maybe Relation field could be more useful.

3- Actually i'm wondering if we can use Relation field to attach article that will be viewed in another article, but only specific fields could be viewed. If yes, how select these specific fields ?

4- Attached article should be an Emerald subscription based : Author must pay, so his article could be viewed. But visitor could always see some specific field values of the Subscription based Article in record where it's attached even if Subscription expired. Is it possible ?

Help please.

narcis Actually i want to get the value of particular field in custom field. I think it'd be nice to get a changed value there, because the particular field is always edited. I tried the Sql query as mentioned in doc, but :

I think that would be nice if you just explain your setup. Would be easier when you know the problem you try to solve. Perhaps there is even completely different solution. It is rather more complicated when we talk abstract.

narcis 1- Value is not changed in custom field when the particular field value changes

It is not clear again. So you created field 1 and when you edit it you want that field 2 changes? Give everything a names. it is not even clear why youo need values of one field in another field, woudl it just create to same fields in an article? You see, whithout knowing what you are douin it even does not make any sense.

narcis 2- it does not work when particular field is a Checkbox one. I get this error message :

The error message say it all. Just run separately this query SELECT field_value FROM xxx_js_res_record_values WHERE record_id = r.id AND field_id IN(34,54,12,13) and you will see that it returns more than one result. how you can concatinate that? It should be one. May be you are looking to yse GROUP_CONCAT?

narcis 3- Actually i'm wondering if we can use Relation field to attach article that will be viewed in another article, but only specific fields could be viewed. If yes, how select these specific fields ?

There is no way to show one article in another article. You can only show list of other article in another article. What I mean that you cannot show full article view inside another full article view. But in the list you may have only one article that is ok.

You can create custom list template and assign it for the relation field and in that template only display fields that you need. In some templates there is exclude fields parameters. So actualy you only need to select that template and configure it differently for relation field.

narcis 4- Attached article should be an Emerald subscription based : Author must pay, so his article could be viewed. But visitor could always see some specific field values of the Subscription based Article in record where it's attached even if Subscription expired. Is it possible ?

Users always see everythign in the list of article but restricted to full view with Emerald. So, you should show everything you want in the list view and everything you want to protect, you only show in full view.

Another words, the answer is yes, this is how it works. If subscrition expired or you have no subscription at all, you are going to see everything that is in the list.

By reading more I guess you are making something like Course -> Tutorials relations. So, yes in the course article list of tutorials will be visible to everyone, but full access granted only to subscribers.

Hello Sergey, Thanks for your answer.

I think that would be nice if you just explain your setup. Would be easier when you know the problem you try to solve. Perhaps there is even completely different solution. It is rather more complicated when we talk abstract.>

Ok, I have two types of Cobalt Articles : Type A is paid (Author must pay, so visitor can view his article), Type B is free. But i want subscriber retrieves some field values of paid article in free article, so these field values can always be viewed even if subscription expires. Free article is just a short version of paid article (that i will perform with success using Relation field later). So, i enabled Sql query in free article to allow subscriber to retrieve some field values from paid article to free article.

It is not clear again. So you created field 1 and when you edit it you want that field 2 changes? Give everything a names. it is not even clear why youo need values of one field in another field, woudl it just create to same fields in an article? You see, whithout knowing what you are douin it even does not make any sense.>

Considering above, sometimes Subscriber edits his paid article, so i'd like retrieved values in free article be changed too automatically if these values are changed in paid article.

The error message say it all. Just run separately this query SELECT field_value FROM xxx_js_res_record_values WHERE record_id = r.id AND field_id IN(34,54,12,13) and you will see that it returns more than one result. how you can concatinate that? It should be one. May be you are looking to yse GROUP_CONCAT?>

I will try this.

You need more information ?

Ok, may be you waht restrict access on the level of the fields thenn you will have to have only one type. Unprotected fields will be always wisible to everyone and protected to subscriber.

But on the level of the type it is also work like this, all fields that are allowed in list view will be visible to everyone.

This means that you can create single article that will show some part to everyone and some only to subscriber.

And if you have single article, the second problem to get values of the other field is gone.