How do I Query action_data?

nate
@nate
11 years ago
917 posts
I am running a jrCore_list function for actions.

I have already limited the search to actions with audio by this code

search="action_module = myAudioModule"

I need to exclude all items that don't have the action_data.audio_distrib_lic_count

this:
search1="action_data.audio_distrib_lic_count > 0" 
returns nothing at all.
updated by @nate: 05/14/14 05:17:30PM
brian
@brian
11 years ago
10,149 posts
The action_data field is stored as a JSON encoded string in the DS - it's not going to be searchable that way, but you may be able to use a REGEX search - i.e.

'search' => array(
    'action_data regex audio_distrib_lic_count":"[1-9]'
)

You might need to play with that a bit, but the idea should work.

http://dev.mysql.com/doc/refman/5.0/en/regexp.html

Hope this helps!


--
Brian Johnson
Founder and Lead Developer - Jamroom
https://www.jamroom.net
nate
@nate
11 years ago
917 posts
As usual, thanks a bunch.
SteveX
SteveX
@ultrajam
11 years ago
2,584 posts
action_data is stored in the db as a json array. If you are searching that unexpanded (and you know the number of licenses already) you could do something like this:
{$zid = $_items[0]['audio_distrib_lic_count']} 
{assign var='search5' value='action_data like %"audio_distrib_lic_count":"'|cat:$zid|cat:'"%}
        
{jrCore_list module="jrAction" profile_id=$_profile_id search5=$search5 order_by="_item_id desc"}

But you probably don't know the number of licenses, so, does the audio_distrib_lic_count field exist of there are 0 licenses?
If it doesn't, you could just search for the existence of the field name:

{assign var='search5' value='action_data like %"audio_distrib_lic_count"%}

If it does, maybe another search for not like would work, although I haven't tried that.

{assign var='search5' value='action_data like %"audio_distrib_lic_count"%}
{assign var='search6' value='action_data not like %"audio_distrib_lic_count":"0"%}



--
¯\_(ツ)_/¯ Education, learning resources, TEL, AR/VR/MR, CC licensed content, panoramas, interactive narrative, sectional modules (like jrDocs), lunch at Uni of Bristol. Get in touch if you share my current interests or can suggest better :)

updated by @ultrajam: 04/10/14 10:59:14AM
nate
@nate
11 years ago
917 posts
I will try all suggested methods.
nate
@nate
11 years ago
917 posts
Why does
search1='action_data like %"audio_distrib_lic_count"%' 
fail when I run it through jamroom?

It works in the db.
updated by @nate: 04/12/14 09:01:47AM
nate
@nate
11 years ago
917 posts
Steve, it works only on a few of the older entries.
brian
@brian
11 years ago
10,149 posts
Natedogg265:
Why does
search1='action_data like %"audio_distrib_lic_count"%' 
fail when I run it through jamroom?

It works in the db.

Probably because of the double quotes - remove those.


--
Brian Johnson
Founder and Lead Developer - Jamroom
https://www.jamroom.net
nate
@nate
11 years ago
917 posts
I'll try that.
nate
@nate
11 years ago
917 posts
It turns our the object has one too many curly braces } in it. The object is likely returning incomplete.

I just used a string before the object end "audio_territories_covered".

Tags