FIND IN SET(id, column)

Can I do this code in jamroom and if so what am I doing wrong? The audio_applied_mixlics will have a comma separated list of numbers, ie 111,132,506.

updated by @nate: 03/20/14 01:02:50AM

No - you cannot use FIND IN SET in a search for jrCore_db_search_items(). There's nothing in jrCore_db_search_items that is going to be able to return an index position based on a set.

It's not clear to me though that that is what you are trying to do. I'm assuming "audio_applied_mixlics" is the name of the datastore field, and you're trying to find DS items that have $id IN the "audio_applied_mixlics" value (which I am assuming can have more than 1 value in it).

Are the ids stored as a comma separate string? What would the value of audio_applied_mixlics look like?

Thanks!


--
Brian Johnson
Founder and Lead Developer - Jamroom
https://www.jamroom.net

Yes the datastore item is storing a comma separated list..

audio_applied_mixlics : 111,321,404,

I want to return any song that has the id 404 in it's audio_applied_mixlics list.

Ok - you would do this:


That should work. I do have a long outstanding "todo" to make this a bit easier (i.e. shorter) but since it's not used much I've just got used to doing it like this.

Hope this helps!


--
Brian Johnson
Founder and Lead Developer - Jamroom
https://www.jamroom.net

Man you would be such a good hacker.

Can I ask, if I am looking for id 12 and the song has id 312 will it return the song?
updated by @nate: 02/03/14 11:48:58AM

No - that's cause we're anchoring it with commas...

Hope this helps!


--
Brian Johnson
Founder and Lead Developer - Jamroom
https://www.jamroom.net

Thanks.

What you did will definitely work, but adding a FIND_IN_SET function would be way more efficient, database index-wise. Nate is right for wanting to use it.

It wouldnt take too much to add a new entry to your DB module called "searchset", that would let you take advantage of this. So something like this:

$_searchset = array();
$_searchset[] .= $id;
$_searchset[] .= "audio_applied_mixlics";

$_sc = array(
"searchset" => $_searchset,
"order_by" => array(
"_created" => "numerical_desc"
),
"pagebreak" => 6,
"page" => 1
);
$_rt = jrCore_db_search_items('xtSong', $_sc);

Your db function could then use an optimized find_in_set query.

Sorry, not trying to tell you how to write your software. Just trying to help out I'm thinking about purchasing your software for a project I am working on...

@codelizard : Awesome for putting in suggestions. @brian is the architect of the datastore system so best to wait for him for a good technical response on the feasibiltiy of using FIND_IN_SET in conjuction wiht a datastore.

Just making sure your aware of the concept behind the datastore:

"Datastores"
https://www.jamroom.net/the-jamroom-network/documentation/development/1023/datastores

They are key=> value pairs data store system. Really useful for co-operation between modules.

Again, thanks for that link. I'll be honest, I didn't realize this data was coming from a datastore and not from a mysql database. That being the case, the find_in_set logic would do nothing but shortcut the 3-way like that @brian mentioned above. Would it even be worth it? Maybe just to save some typing and look cleaner, but it wouldn't save any processing...

Thanks for taking the opportunity to educate me, rather than a bad response. Ive seen too many projects where people flame first, and explain things later. Your candor is much appreciated.

no worries, suggestion appreciated. It might be a great one, im not sure. Brian will know.

JR4 didn't have datastores so they're quite a new thing to get used to.

but once you get used to them they are awesome and you want everything to be a datastore.

standard mysql tables can be used too if you want to or as extra info setup but the beauty with a datastore is moduleA can write to moduleB's datastore to extend it without needing to alter its table structure.

One example of this is the jrTags module which when activated allows users (who are given permission to) to tag items with words. (the concept is pretty standard).

When something like an audio track is tagged, jrTag adds those tags to the jrAudio's datastore. so when someone requests a list of audio items, they get back the tags come out too.

Because of datastores jrTags is able to tag any other module that also uses a datastore. This means that future modules that also use a datastore are already compatible with the jrTags module.

No awareness needed.

Because of datastores and the 'events' and 'listeners' system (similar to hooks if you've used drupal) when one module requests stuff from its own datastore other modules can inject relevant info into the returning data.

its pretty cool.

yeah sounds cool. Ive used many systems with hooks (joomla, drupal, vbulletin as examples), so its nice to see them implemented here. The datastores sound cool, and I have like a bajillion questions on how its implemented and such, but I've really gotta step away from the pc and get some sleep.

Just to add on here, the DataStore runs on top of MySQL - it's not a separate DB. It was designed so that module developers do not have to write SQL queries, and ideally we can then change DS backends (i.e. use something like Riak, ElasticSearch, etc.) - that's not something I've done yet, but the "idea" is in place and just needs to be fleshed out in a future release.

If your curious about any of the internals, I can do my best to answer - there have (of course) been some trade offs internally to make things easier to use (or support), but the jrCore_db_sarch_items has a lot of functionality for doing almost anything you want.

Hope this helps!


--
Brian Johnson
Founder and Lead Developer - Jamroom
https://www.jamroom.net
updated by @brian: 02/13/14 09:06:31AM